Problem referencing multiple closed .xls files

  • Thread starter Thread starter Gunkie
  • Start date Start date
G

Gunkie

Hi there.

I have an Excel file [MAIN] that references multiple other Excel files
[SUB]. For example, if I put the following into a cell in MAIN and
have the SUB files closed, this works:

='C:\Documents and Settings\Scott\My Documents\MAIN DIR\SUB
DIR\[SUB.xls]Sheet25'!$D$11

The above returns the value [not #REF!] even when SUB is not open.

But if I try relative addressing:

=SUB DIR\[SUB.xls]Sheet25'!$D$11

or, INDIRECT, or CELL, or VLOOKUP, nothing can return the value from a
closed file.

Any ideas without having to resort to either UDFs or Macros. My client
is unwilling to move their security setting from high to medium and is
upset with getting the macro warning when opening these files, so I'm
trying to work around as much as possible.

THANKS!

Scott Ghiz
www.SGSystems.com
 
I've never had =vlookup() have trouble with closed files--or that kind of direct
link.

Are you sure you're pointing to the correct drive/folder/file/sheet?


Hi there.

I have an Excel file [MAIN] that references multiple other Excel files
[SUB]. For example, if I put the following into a cell in MAIN and
have the SUB files closed, this works:

='C:\Documents and Settings\Scott\My Documents\MAIN DIR\SUB
DIR\[SUB.xls]Sheet25'!$D$11

The above returns the value [not #REF!] even when SUB is not open.

But if I try relative addressing:

=SUB DIR\[SUB.xls]Sheet25'!$D$11

or, INDIRECT, or CELL, or VLOOKUP, nothing can return the value from a
closed file.

Any ideas without having to resort to either UDFs or Macros. My client
is unwilling to move their security setting from high to medium and is
upset with getting the macro warning when opening these files, so I'm
trying to work around as much as possible.

THANKS!

Scott Ghiz
www.SGSystems.com
Click to expand...
 
Back
Top