software

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($B25lt;gt;quot;quot;,VLOOKUP(B25,B7,2,FALSE),quot;quot;) where the path name is stored in B7.
Any suggestions?

You would need to use the INDIRECT function for that:

=IF($B25lt;gt;quot;quot;,VLOOKUP(B25,INDIRECT(B7),2,FALSE),quot;quot;)

HTH,
Elkarquot;SpokaneExcelquot; wrote:

gt; I am using vlookup to access data in other spreadsheets. The spreadsheet to
gt; access varies by the data being searched for. I am trying to use a cell
gt; reference for the table_array value to make things easier. Something like:
gt; =IF($B25lt;gt;quot;quot;,VLOOKUP(B25,B7,2,FALSE),quot;quot;) where the path name is stored in B7.
gt; 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
quot;Elkarquot; wrote:

gt; You would need to use the INDIRECT function for that:
gt;
gt; =IF($B25lt;gt;quot;quot;,VLOOKUP(B25,INDIRECT(B7),2,FALSE),quot;quot;)
gt;
gt; HTH,
gt; Elkar
gt;
gt;
gt; quot;SpokaneExcelquot; wrote:
gt;
gt; gt; I am using vlookup to access data in other spreadsheets. The spreadsheet to
gt; gt; access varies by the data being searched for. I am trying to use a cell
gt; gt; reference for the table_array value to make things easier. Something like:
gt; gt; =IF($B25lt;gt;quot;quot;,VLOOKUP(B25,B7,2,FALSE),quot;quot;) where the path name is stored in B7.
gt; gt; 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 Pullftp://members.aol.com/hrlngrv/ (look for pull.zip)

xcell05.free.fr/english/ (moribund)
--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;SpokaneExcelquot; gt; wrote in message
...
gt; I've tried indirect() and every combination of [] and ' and # and
gt; everything
gt; else I can think of. I copied the table_array name out of a complete
gt; equation
gt; that does work. The value of B7 is 'J:\Project List\[Project List
gt; 2006.xls]Sheet1'!$A$2:$B$400
gt;
gt;
gt;
gt; quot;Elkarquot; wrote:
gt;
gt;gt; You would need to use the INDIRECT function for that:
gt;gt;
gt;gt; =IF($B25lt;gt;quot;quot;,VLOOKUP(B25,INDIRECT(B7),2,FALSE),quot;quot;)
gt;gt;
gt;gt; HTH,
gt;gt; Elkar
gt;gt;
gt;gt;
gt;gt; quot;SpokaneExcelquot; wrote:
gt;gt;
gt;gt; gt; I am using vlookup to access data in other spreadsheets. The
gt;gt; gt; spreadsheet to
gt;gt; gt; access varies by the data being searched for. I am trying to use a
gt;gt; gt; cell
gt;gt; gt; reference for the table_array value to make things easier. Something
gt;gt; gt; like:
gt;gt; gt; =IF($B25lt;gt;quot;quot;,VLOOKUP(B25,B7,2,FALSE),quot;quot;) where the path name is stored
gt;gt; gt; in B7.
gt;gt; gt; Any suggestions?