VLOOKUP from closed file using INDIRECT function

=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" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> wrote in message
> news:[email protected]...
> > =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?