Using INDIRECT within a VLOOKUP function

  • Thread starter Thread starter Daniel Hayes
  • Start date Start date
D

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
 
Hi
INDIRECT does notwork on closed workbooks. Though there
several alternatives these won't work as 2nd parameter in
vLOOKUP (at least AFAIK)

Frank
 
Back
Top