editing filenames when linking two workbooks

[LeighAnne]

I have a set of workbooks "T10", "T20", "T35" etc. all set up in exactly the same way, dates in column A and project numbers in row 1. For your information, project numbers in Book T10 start with 10, project numbers in Book T20 start with 20 etc.

DATES 10344 10543 10641 10984
01/01/2005 21
02/01/2005 38
03/01/2005 15
04/01/2005 12


I would like to reference these project workbooks from another workbook (set out as below), how can I write a lookup formula that will change the workbook it references depending on the team code?

DATES 10344 10543 20632 20514
01/01/2005 21
02/01/2005 38
03/01/2005 43
04/01/2005 99


I started out with this formula in cell B3 (works fine):

=HLOOKUP(B$2,'[T10.xls]Cash Out'!$B$1:$X$100,2,FALSE)

and tried to change it to..:

=HLOOKUP(B$2,"'[" & B$1 & ".xls]Cash Out'!$B$1:$X$100",2,FALSE)

..so that it would use the correct workbook, but it doesn't work.

Does anyone have any ideas?

Hoping someone can help.......

Le

 

[xlbo]

Have a look at the INDIRECT function. It converts a piece of text into a cell reference so you can build it like a string eg. In cell A1, you may have:

="'[" & $A$2 & ".xls]Cash Out'!$B$1:$X$100"

In A3, you may then use

=HLOOKUP(B$2,INDIRECT(A1),2,false)

Then, by changing what is in A2, you can change the referenced workbook


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[LeighAnne]

Thanks xlbo!

That worked a treat!

Le