You can download an add-in called Morefunc which has a function called
INDIRECT.EXT that will work if the source book is closed

http://xcell05.free.fr/morefunc/english/index.htm


If this post helps click Yes
---------------
Jacob Skaria


"Kevin McCartney" wrote:

Like Jaf points out, is there a function that works when the referenced file
is closed, or does it work regardles in Excel 2007?

TIA
KM


"Jacob Skaria" wrote:

Use INDIRECT() function

Try the below which will help ...

In Book1, Sheet1, cell A1 type: "This is a test" (without the quotation
marks).
In Book2, Sheet1, cell A1 type: "Book1" (without the quotation marks).
In Book2, Sheet1, cell A2 type: "Sheet1" (without the quotation marks).
In Book2, Sheet1, cell A3 type: "A1" (without the quotation marks).
Save both workbooks.
In Book2, Sheet1, cell B1 type the following formula:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)


--
If this post helps click Yes
---------------
Jacob Skaria


"Kevin McCartney" wrote:

Hi TWIMC,

Usually when referencing into a different workbook, Excel constructs the
formula like so:

=VLOOKUP(B10;'C:\folder path\My
Documents\[workbookname.xls]worksheetname'!$G$10:$H$20;2;FALSE)

My question is, is there a way to have a VLOOKUP formula reference the table
array parameter in the following manner?

=VLOOKUP(B10;A2!$G$10:$H$20;2;FALSE)

where A2 = 'C:\folder path\My Documents\[workbookname.xls]worksheetname'

This would allow the end user to specify which workbook/worksheet the
VLOOKUP function is to reference.


TIA
KM