Using INDIRECT within a VLOOKUP function

[Daniel Hayes]

Hi,

I'm using Excel 2000 with Windows 98.

I'm producing a spreadsheet which summarises the data from 43 other
spreadsheets. I don't know where these files will be located on my
client's computer, and so I don't want the path to be hard-coded into
the formulae. At the moment, one of them looks like this:

=VLOOKUP($A6,'C:\WINDOWS\Profiles\hayesd\Desktop\New Folder\[City of
London.xls]Support'!$A$10:$M$33,13,FALSE)

I'd like to use a named range, say FileLocation, and use the INDIRECT
function. The named range FileLocation would contain the path, which
the client could change depending on where the files are located. I
want to use something like this:

=VLOOKUP($A6, '(INDIRECT("FileLocation"))[City of
London.xls]Support'!$A$10:$M33,13,FALSE)

However, when I type this, I get an #N/A message (this does not happen
if I hard code the path, FYI).

Has anyone got any ideas? Is INDIRECT the right way to go about doing
this?

Cheers,

Daniel Hayes
London, England

 

[Frank Kabel]

Hi
INDIRECT does notwork on closed workbooks. Though there
several alternatives these won't work as 2nd parameter in
vLOOKUP (at least AFAIK)

Frank