Combining data from 2 spreadsheets

users
#1

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. Thank you.

Regards,

#2

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

/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