Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index


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
=VLOOKUP(A1;
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
;2;0)
to the formula. Press Enter or click the green arrow. You will now see something like =VLOOKUP(A1;'file:///C:/<path to your document folder>/AccountNumbers.ods'#$Sheet1.A1:B1500;2;0)
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.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.