Indirect Function

[Gaurav]

Hi,

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.

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?

Regards,

Gaurav

 

[Dave Peterson]

See your other post.

Hi,

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.

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?

Regards,

Gaurav

 

[Shane Devenshire]

Hi,

I don't know what your other post said, but INDIRECT can't reference closed
files. However, if you use any version of Lotus 1-2-3 or Quattro or Open
Office it will work.