Hi Brian,
On 01/02/17 16:55, Brian Barker wrote:
At 16:13 01/02/2017 +0000, Budge Noname wrote:
Struggling here with VLOOKUP which I think is the right tool. I have a
column of numbers on one sheet (sheet 1) ...
Let's say they are in column A.
... and on another sheet (sheet 2) a column of numbers with an
associated name in adjacent cell.
Let's say they are in columns A (numbers) and B (names).
My objective is to find the name associated with the number on sheet 2
and enter it in sheet 1, ...
Let's say in column B.
I think VLOOKUP might be the right tool ...
Yup.
... but got stuck right at the start trying to reference across two
sheets. I have both open in same instance of LibreOffice.
If you have two sheets they are part of they same document. Perhaps you
mean two spreadsheet documents? You can do either.
The numbers are random and not necessarily to be found in second sheet.
In B1, enter:
=VLOOKUP(A1;Sheet2.$A$1:$B$10;2;0)
and fill it down the column.
o A1 because we are looking for the entry corresponding to A1.
o A1:B10 (say) is the table of numbers and names.
o 2 is the identifier of the column of the table (the second) containing
the required names.
o 0 (or FALSE) indicates that the numbers in column A of your first
sheet may not be sorted.
If no name is found I need some marker entered so I know I must
recourse to a manual search elsewhere.
You will get #N/A free of charge: that may do. If you want something
else, try:
=IFNA(VLOOKUP(A1;Sheet2.$A$1:$B$10;2;0);"Missing name!")
If the name table is in a separate document, you may find it simpler to
copy that into your main document or to create a reference there to the
entire table first.
Oh, and the answers to your other three questions are remarkably similar.
Many thanks and apologies to the list. The multiple posting was because
I was using a Windoze machine and Thunderbird was telling me the message
had not been sent each time I tried. (Half an hour on phone to ISP
still didn't sort it so I am doing email on Linux machine)
Many thanks for the answer. One supplementary question, the when
created each sheet is in a different file. I have imported on into the
other to get them in the same file but the sheet tab names are the
filenames not "sheet 1" or "sheet 2." Do I have to type in the full
filename? In parenthesis? On the windoze machine, tab doesn't
complete the typing so it is a bit of a pain.
Will try it meanwhile and thanks again,
Budge.
--
To unsubscribe e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
