referenced filename in VLOOKUP formula
Posted by A Reid on January 29, 2002 8:29 AM
Ok, What I have is a list of filenames, of other .xls files. Each filename is in a separate cell in a list in another workbook. What I want to do is perform certain calculations on certain cells from each of the listed files, using the same cell(s) in each file. As each filename is slightly different I would like to use the same formula and just reference the cell that has the filename in it so I can replicate the formula but won't have to go to each one manually and input the correct filename.
The basic formula I have is: VLOOKUP($A3,'H:\Work\TiPS\wk1\[I041GAOS.XLS]TIMESHT'!$A$14:$O$33,15,FALSE)
What I would like to have is something like VLOOKUP($A3,'H:\Work\TiPS\wk1\[A2]TIMESHT'!$A$14:$O$33,15,FALSE) where cell A2 (and the rest of Row 2) contains the filename text so I can copy the same formula across for all filenames. I'm just unsure of the correct syntax or if its possible.
In other words how do you insert a reference to a filename in a formula I guess?
Many thanks in advance for any help!