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!
Posted by Adam S. on January 30, 2002 9:02 AM
Indirect function-but with a flaw
Hiya,
The only way (a somewhat flawed way) that I can think of is through the use of the INDIRECT function. Let's say you have the cell B2 contain: 'H:\Work\TiPS\wk1\[I041GAOS.XLS]TIMESHT'!
(that's the filename but not the - $A$14:$O$33 reference).
The formula would look something like:
=vlookup($A3,INDIRECT(B2&"$A$14:$O$33"),15,false)
I played with this kind of formula recently but found that these links would only work if the linked files were open (kind of a big flaw actually). A while back I posted the question to whether or not there was a work-around, but sadly there does not appear to be one.
Hope that helps out.
Adam S.
