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.

you can try indirect

this looks at cell b8 and then goes to that sheet name, cell i7 and returns the
value

=INDIRECT(B8 & "!$I$17")

--


Gary


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

Hi Gary....good idea...I tried that but I didn't get the result I wanted. I
don't know much about the Indirect function, but it may not work on closed
workbooks--I think I read that somewhere...In any case, what I will like to
do is actually provide the file name (or at least part of it), and for excel
to go to that file and open and retrieve a cell value and close the file.

"Gary Keramidas" wrote:

you can try indirect

this looks at cell b8 and then goes to that sheet name, cell i7 and returns the
value

=INDIRECT(B8 & "!$I$17")

--


Gary


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

sorry, missed the fact the workbook was closed

--


Gary


"Kaby" wrote in message
...
Hi Gary....good idea...I tried that but I didn't get the result I wanted. I
don't know much about the Indirect function, but it may not work on closed
workbooks--I think I read that somewhere...In any case, what I will like to
do is actually provide the file name (or at least part of it), and for excel
to go to that file and open and retrieve a cell value and close the file.

"Gary Keramidas" wrote:

you can try indirect

this looks at cell b8 and then goes to that sheet name, cell i7 and returns
the
value

=INDIRECT(B8 & "!$I$17")

--


Gary


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

="='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.

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

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

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

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