VLOOKUP from closed file using INDIRECT function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

=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
 
Thank you. I've downloaded it.

Peo Sjoblom said:
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 said:
=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
Click to expand...
Click to expand...
 
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?
 
Back
Top