MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.