What happens if you actually type in the value in D86 into a cell with a similar
formula:

Say D86 contains Book1

if you put
=INDIRECT.EXT("'C:\[book1.xls]BUYERSREQUEST'!E34")

heck, if you just do it directly:
='C:\[book1.xls]BUYERSREQUEST'!E34

do either work?

But since I haven't used this, I'm pretty much just guessing.

Kaby wrote:

The first formula looks perfect...but when I tried it I get a #VALUE error...

"Dave Peterson" wrote:

I don't use that addin, but maybe...

=INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E34")

And it looks like you tried to combine that function with =vlookup().

I still don't use it, but...

=vlookup(a1,INDIRECT.EXT("'C:\[" & D86 & ".xls]BUYERSREQUEST'!E:F"),2,false)

or something like that...

Kaby wrote:

Thanks Dave...I've now added the Indirect.EXT function and working with this
function seems to be the way I should be headed.

I did want to ask a question about that function...this may be a little
difficult to explain, but I will give it a try anyway. I have a master
workbook that I would like to link information from various workbooks placed
in a folder. In the master workbook, there will be a column that will have
the file name (at least part of it - to be exact the product name), and what
I would like to do with this indirect faunction is match up that product name
with the file name and retrieve a cell value from the matched workbook. I've
tried =INDIRECT.EXT("'C:\[D86.xls]BUYERSREQUEST'!E34,FALSE), but that didn't
work....Are there any suggestions?

"Dave Peterson" wrote:

You'd want to use the =indirect() function, but that only works when the
"sending" workbook is open.

But Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.
Kaby wrote:

="='C:\Temp\[basefilename"&Text(Today(),"yymmdd")&".xls]Sheet1'!$A$1"

This may the direction I want to go--but this one has date as a variant

"Kaby" wrote:

Is there a formula that I can put in a cell that tells excel to match part of
the file name that I will provide for it in an adjacent cell, go into the
file and link the data to the destination file.

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson