Re: [libreoffice-users] Combining data from 2 spreadsheets

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