=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

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" wrote in message
...
=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

Thank you. I've downloaded it.

"Peo Sjoblom" wrote:

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" wrote in message
...
=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

I'm at work right now so I can't try the PULL function... but I know
INDIRECT.EXT doesn't allow ranges... does PULL? is it a .dll also? VBA?


--
travisc
------------------------------------------------------------------------
travisc's Profile: http://www.excelforum.com/member.php...o&userid=34508
View this thread: http://www.excelforum.com/showthread...hreadid=541973