Not possible using anything built in,

ftp://members.aol.com/hrlngrv/

look for pull.zip

http://xcell05.free.fr/

it's a multifunction add-in called Morefunc (very useful) and this
particular function is called

INDIRECT.EXT



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Ben" <Ben@discussions.microsoft.com> wrote in message
news:23250443-8E49-4269-9F74-D3D24432050F@microsoft.com...
> =VLOOKUP(M4,INDIRECT("'C:\MetalRisk\PriceData\["&AB4&".xls]Diffs3'!$B$3:$Z$1892"),$AB$3,FALSE)
>
> The above formula works fine except that the file that contains the table
> array has to be open. The purpose behind my using the indirect function
> was
> to specify the name of the file in AB4 and to avoid having to open it. I
> have
> tried using a line of code like ActiveWorkbook.Updatelink followed by the
> file name to be opened but it still does not update the cell. Doing it
> manually in EDIT-LINKS also does not work. But as soon as I open the file
> the
> the cell updates immediately. Can anybody suggest how I can use this
> indirect
> function WITHOUT opening the file containg the data array. Thank you