The won't work since INDIRECT does not work with files that are closed so
the only way is to open the file and thus the path is not necessary just the
file name.

If the OP does a Google search for Morefunc he can download and install that
add-in and one of the functions in that add-in is called INDIRECT.EXT which
will work with closed files

--


Regards,


Peo Sjoblom

wrote in message
...
On Aug 13, 10:02 am, beeblemonster
wrote:
Hello.

I'm using VLOOKUP to grab data from a different file... that part is
pretty
simple. The problem is the file name gets changed a lot. I need to have
one
cell that has the path, and somehow get VLOOKUP to reference it's
table_array
portion to that cell's path. that way instead of changing the 532 formulas
everytime someone changes the file name I can just change that one cell.

I'm having trouble getting this to work, I think the problem is with
telling
it to look at the first tab but i'm not entirely sure.

please help!!!!!

change the table_array portion to an indirect function. If you place
the filename in cell A1, then make your formula
VLOOKUP(B1,INDIRECT(A1),2). Remember to place the filename in the
format of something like:
C:\Documents and Settings\tsides\My Documents\[Book1.xls]Sheet1'!$A
$2:$B$5

You could even hardcode the path and just put the filename in A1:
VLOOKUP(B1,INDIRECT("C:\Documents and Settings\tsides\My Documents\
["&A1&"]Sheet1'!$A$2:$B$5"),2)