use data in a cell in a formula

[SpokaneExcel]

I am using vlookup to access data in other spreadsheets. The spreadsheet to
access varies by the data being searched for. I am trying to use a cell
reference for the table_array value to make things easier. Something like:
=IF($B25<>"",VLOOKUP(B25,B7,2,FALSE),"") where the path name is stored in B7.
Any suggestions?

 

[Elkar]

You would need to use the INDIRECT function for that:

=IF($B25<>"",VLOOKUP(B25,INDIRECT(B7),2,FALSE),"")

HTH,
Elkar

 

[SpokaneExcel]

I've tried indirect() and every combination of [] and ' and # and everything
else I can think of. I copied the table_array name out of a complete equation
that does work. The value of B7 is 'J:\Project List\[Project List
2006.xls]Sheet1'!$A$2:$B$400

 

[Peo Sjoblom]

You can't use INDIRECT on closed workbooks that's why (if the workbook isn't
closed no need for a path), there are some add-ins like

Morefunc (INDIRECT.EXT) and Harlan Grove wrote one called Pull


ftp://members.aol.com/hrlngrv/ (look for pull.zip)

http://xcell05.free.fr/english/ (moribund)






--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

I've tried indirect() and every combination of [] and ' and # and
everything
else I can think of. I copied the table_array name out of a complete
equation
that does work. The value of B7 is 'J:\Project List\[Project List
2006.xls]Sheet1'!$A$2:$B$400

You would need to use the INDIRECT function for that:

=IF($B25<>"",VLOOKUP(B25,INDIRECT(B7),2,FALSE),"")

HTH,
Elkar