cancel
Showing results for 
Search instead for 
Did you mean: 

Libre Office Spreadsheet query

FIXED
Community Veteran
Posts: 18,996
Thanks: 2,138
Fixes: 27
Registered: ‎06-11-2007

Libre Office Spreadsheet query

I run a spreadsheet. as a "log" for all the contacts I make, on my morse program.... I fill in the columns as follows.

callsign        name        location

Is there a way to get them to "autofill" ... so that when I fill the callsign entry .. the other two complete automatically...

Thanks

 

5 REPLIES 5
Moderator
Moderator
Posts: 28,424
Thanks: 2,458
Fixes: 317
Registered: ‎14-04-2007

Re: Libre Office Spreadsheet query

In Excel I use the vLookup function to perform that action. Not sure if Libre Office has an equivalent.

Needs someone more knowledgeable than me to explain it in detail.

Customer and Forum Moderator. Windows 10 Firefox 69.0.3 (64-bit)

Life is tough enough without adding Linux into the mix.
Highlighted
wisty
Pro
Posts: 525
Thanks: 80
Fixes: 7
Registered: ‎30-07-2007

Re: Libre Office Spreadsheet query

Fix

This works on Excel, and a quick search of the  Libre office functions suggests that the Vlookup function has the same parameters although it may need ; rather than , between parameters (I don't use Libre so I can't test it). But it should work.

Assuming the Call-signs are Column A, the Name in column B and the Location in column C and all start at row 2. Also assuming there are 29 entries in the table so it is filled in down to row 30. You will need to replace these values with the ones for your spreadsheet - make a copy before playing!!

Row 31 is the next entry

In Cell B31 you need   =VLOOKUP(A31,$A$2:$C30,2,FALSE)

 A31 will be the value of the new (repeated) call-sign you type in 

A2:C30 is the array of existing data you want to look up.

2 tells the Vlookup function that when it finds a match between A31 and a value in the first column of the array A2:C30, you want the contents of the second column of that row entered into this cell. FALSE tells the formula that you want an exact match for the call-sign.

Similarly in C31 you would put =VLOOKUP(A31,$A$2:$C30,3,FALSE) to get you the contents of column C in the new row.

The $ signs in the array are to ensure that if you copy and paste cells B31 and C31 down the spreadsheet, it continues to pick up all the new entries in the search. If there is nothing in the callsign column, you will get an error (its (#N/A in Excel) in  columns B & C, but as soon as you enter a valid call-sign it should pick up the corresponding data. 

This works on Excel, and a quick search of the Libre office functions suggests that the Vlookup function has the same parameters although it may need ; rather than , between parameters. So it should work.

If you enter a call-sign that is brand new and not in the array, it will give you errors in B31 and C31. Overwrite with the correct data, and as long as you have copied the formula further down the spreadsheet, it will include the new values in the search.

Hope this helps.

Community Veteran
Posts: 18,996
Thanks: 2,138
Fixes: 27
Registered: ‎06-11-2007

Re: Libre Office Spreadsheet query

@Strat  Thanks for the info.

@wisty  Thanks for the tutorial !.... looks a bit too complicated for my needs.... as I am expecting over 1000 contacts per a one years logging... so that would mean at least 1000 cells in col A  to be "pre-programmed" ! .although quite a lot of those contacts will be repeated... it will take less time per contact, to actually fill in the blanks on that line, at the time...  than it would to spend all that effort and probably make mistakes in the cells too ! ! ..

 

Many thanks, for your help, guys. :wink:

Superuser
Superuser
Posts: 7,612
Thanks: 1,410
Fixes: 89
Registered: ‎30-07-2007

Re: Libre Office Spreadsheet query

You wouldn't need to preprogram anything @shutter  as @wisty  says @ all you need to do is copy the formula in columns B & C to all rows. You could just do a 'fill down' for those columns

Community Veteran
Posts: 18,996
Thanks: 2,138
Fixes: 27
Registered: ‎06-11-2007

Re: Libre Office Spreadsheet query

ah... ok.... thanks.... maybe give it a go then...

cheers

:wink: