Thread: Return a value in another spreadsheet

Hi

I have a question...

I currently use a rota which has dates in column A and names in column B - call this ROTA

Then I use a tracker, where it holds all the names in column A and i want it to return the date that the name is next to in ROTA.

In plain language this is the formula...

if 'Joe Bloggs' from spreadsheet 'tracker', is in spreadsheet 'rota' column B, then return date in column a to spreadsheet 'tracker' column b (next to 'Joe Bloggs')

Really hope this makes sense!!

Please help!

Thanks Julia

Hiya again

I have attached examples of what I need. The end result should return under 'date attended' in tracker.

Thanks

Oldchippy's solution is clean and simple. It's better to organize your data to make it easy for future reference.
Having said that, if just can't move the columns then see the attached files.
The formula written in Tracker.xls will work regardless of the order of the column placements.
modytrane

Thank you both Modytrane & Oldchippy. I think the formula in the tracker will work out best now I just need to work out how to apply it to my spreadsheets here!
Thanks

Dont suppose anyone could help me break it down?

=INDIRECT("[Rota.xls]Sheet1!A"&(MATCH(A3,'C:\Documents and Settings\Julia\Local Settings\Temporary Internet Files\Content.IE5\LVSQZ9UT\[Rota.xls]Sheet1'!$C$2:$C$7,0)+ROW($C$2)-1))

=INDIRECT("[Rota.xls]Sheet1!A"&(MATCH(A3,'C:\Documents and Settings\Julia\Local Settings\Temporary Internet Files\Content.IE5\LVSQZ9UT\[Rota.xls]Sheet1'!$C$2:$C$7,0)+ROW($C$2)-1))

You can read up on the Indirect function under help, but here's the general idea.

The function will allow you to reference a cell in an indirect way. So, instead of addressing a cell directly as "A4", you can write INDIRECT(A1), where "A1" contains "A4". This method allows you to build the address using a formula.
In the formula above, first part "[ROTA.xls]Sheet1!A" builds first part of the cell address, which in this case is fixed. It includes the ROTA.xls file, Sheet1 and column A. The part after & calculates the Row number using a MATCH function.

In the MATCH fucntion you are trying to find a match for "A3" in the Array located in Sheet1 of ROTA.xls at $C$2:$C$7. The match function gives you relative location within the array. So let's say you find a match at third location in the array. So you would get a result of 3, but since the array starts in row 2, you want to add 1 to get the actual row number, which is 4.
You can simply rewrite the formula as follows:

=INDIRECT("[Rota.xls]Sheet1!A"&(MATCH(A3,[Rota.xls]Sheet1!$C$2:$C$7,0)+1))

Hope this helps.
modytrane