At 12:52 19/01/2014 +0300, Bashar Maree wrote:
At my workplace the salaries are usually calculated in a simple Calc
sheet that has 3 columns; the employee ID, his name and the salary
amount. The number of paid employees ranges, month to month, between
40 to 100. We now need to add a bank account number to this list.
The account numbers are in a separate spreadsheet that has 2 columns
employee ID and his account number. This second spreadsheet has over
1500 entries in it. Is there a way to automate filling the account
column in the first sheet from the second by using the employee IDs.
Yes - using the VLOOKUP() function.
Let's suppose that your IDs are in column A and that you wish to
compile the bank account numbers in column D. In D1 (or wherever), enter
Then move to the other spreadsheet and drag your mouse over the two
column range - A1 to B1500 or whatever - containing the bank account
data. Return to your first sheet and add
to the formula. Press Enter or click the green arrow. You will now
see something like
=VLOOKUP(A1;'file:///C:/<path to your document
in the Input Line for cell D1. Fill or copy this formula down the column.
How does it work? The VLOOKUP() function takes its first argument -
here the ID - and searches for it in the first column of the range
specified in the second argument - here on the other
spreadsheet. When a match is discovered, it harvests the
corresponding value from the column specified in the third argument -
here the second column, of account numbers. The last argument
indicates that you want any IDs missing in the account number
spreadsheet to cause an error condition and not to provide an account
number from a different employee with a neighbouring ID value!
The formula would look a lot simpler if you copied the values from
the second spreadsheet to a separate sheet in the first spreadsheet
document. But you wouldn't want to do this if the second spreadsheet
is being independently maintained and updated.
Oh, and the answer to your second query is much the same!
I trust this helps.
To unsubscribe e-mail to: firstname.lastname@example.org
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy