Thanks again...worded great! However, what I ultimately like to do is use
this somehow in conjunction with a vlookup. I need to do a vlookup and have
it return the data when file is closed. Any ideas? Thanks.

"Harlan Grove" wrote:

deeds wrote...
Thanks Harlan....I did what you said....I also went to the link you
provided to read up...however, I still can't seem to get it to work.
Do I insert function (Pull)? What do I need to do to have my
Indirect formula work when the file is closed? Any help would be
great! Thanks again!

You'd use pull INSTEAD OF INDIRECT.

If I

- create a new workbook in Excel,
- press [Alt]+[F11] to display the VB Editor (VBE),
- run the VBE menu command Insert Module to create a GENERAL module
in the workbook,
- paste my latest pull code into that module,
- press [Alt]+[F11] again to return to Excel,
- and enter a formula like

=pull("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")

it returns the same value as the static external reference

='D:\test\[foo.xls]Sheet1'!C5

If you don't get similar results, provide DETAILS on what steps you're
taking to put the pull code into your workbook.

FYI, there's also Laurent Longre's MOREFUNC.XLL add-in, available from
http://xcell05.free.fr/english/, which provides an add-in function
named INDIRECT.EXT which provides pretty much the same functionality
as pull. It may be easier to get MOREFUNC to work.