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?

You would need to use the INDIRECT function for that:

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

HTH,
Elkar


"SpokaneExcel" wrote:

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?

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



"Elkar" wrote:

You would need to use the INDIRECT function for that:

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

HTH,
Elkar


"SpokaneExcel" wrote:

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?

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


"SpokaneExcel" wrote in message
...
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



"Elkar" wrote:

You would need to use the INDIRECT function for that:

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

HTH,
Elkar


"SpokaneExcel" wrote:

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?