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.

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

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.

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

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

This is helpful...I feel like I am getting closer to programming that I want
to accomplish. When I type in this formula:
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

The cell value turns into this:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

and not the actual value found in cell c44. I am not sure why...but I think
my syntax to my formula is wrong.
Heck--doing it directly maybe better than indirectly...

Kaby

"Dave Peterson" wrote:

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

This formula just creates a nice string.
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

You could convert it to values (edit|copy, edit|paste special|values) and then
you'd have this string:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Add an equal sign and it'll become a formula
='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Maybe you got the #value! error from the original workbook because the workbook
didn't exist on the C: drive???


Kaby wrote:

This is helpful...I feel like I am getting closer to programming that I want
to accomplish. When I type in this formula:
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

The cell value turns into this:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

and not the actual value found in cell c44. I am not sure why...but I think
my syntax to my formula is wrong.
Heck--doing it directly maybe better than indirectly...

Kaby

"Dave Peterson" wrote:

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


--

Dave Peterson

Terrific...this is good...I will work with this more, but I feel very close
to a solution. Thanks a bunch....I will let you know if I have any more
questions.

Kaby

"Dave Peterson" wrote:

This formula just creates a nice string.
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

You could convert it to values (edit|copy, edit|paste special|values) and then
you'd have this string:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Add an equal sign and it'll become a formula
='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Maybe you got the #value! error from the original workbook because the workbook
didn't exist on the C: drive???


Kaby wrote:

This is helpful...I feel like I am getting closer to programming that I want
to accomplish. When I type in this formula:
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

The cell value turns into this:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

and not the actual value found in cell c44. I am not sure why...but I think
my syntax to my formula is wrong.
Heck--doing it directly maybe better than indirectly...

Kaby

"Dave Peterson" wrote:

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


--

Dave Peterson

I actually want to keep it as a string that turns into a formula so that when
I change the product name column, so does the filename and the linking. Is
this possible--if it makes sense?

"Kaby" wrote:

Terrific...this is good...I will work with this more, but I feel very close
to a solution. Thanks a bunch....I will let you know if I have any more
questions.

Kaby

"Dave Peterson" wrote:

This formula just creates a nice string.
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

You could convert it to values (edit|copy, edit|paste special|values) and then
you'd have this string:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Add an equal sign and it'll become a formula
='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Maybe you got the #value! error from the original workbook because the workbook
didn't exist on the C: drive???


Kaby wrote:

This is helpful...I feel like I am getting closer to programming that I want
to accomplish. When I type in this formula:
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

The cell value turns into this:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

and not the actual value found in cell c44. I am not sure why...but I think
my syntax to my formula is wrong.
Heck--doing it directly maybe better than indirectly...

Kaby

"Dave Peterson" wrote:

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


--

Dave Peterson

You either have to do that string to formula yourself -- or use something else.

That something else is =indirect.ext().

Maybe someone who uses =indirect.ext() can jump in with better instructions.

Kaby wrote:

I actually want to keep it as a string that turns into a formula so that when
I change the product name column, so does the filename and the linking. Is
this possible--if it makes sense?

"Kaby" wrote:

Terrific...this is good...I will work with this more, but I feel very close
to a solution. Thanks a bunch....I will let you know if I have any more
questions.

Kaby

"Dave Peterson" wrote:

This formula just creates a nice string.
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

You could convert it to values (edit|copy, edit|paste special|values) and then
you'd have this string:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Add an equal sign and it'll become a formula
='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Maybe you got the #value! error from the original workbook because the workbook
didn't exist on the C: drive???


Kaby wrote:

This is helpful...I feel like I am getting closer to programming that I want
to accomplish. When I type in this formula:
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

The cell value turns into this:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

and not the actual value found in cell c44. I am not sure why...but I think
my syntax to my formula is wrong.
Heck--doing it directly maybe better than indirectly...

Kaby

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

This formula:

=INDIRECT.EXT('H:\["&D100&".xls]BUYERSREQUEST'!C44)

keeps giving me a #value error...any obvious reasons....


"Dave Peterson" wrote:

You either have to do that string to formula yourself -- or use something else.

That something else is =indirect.ext().

Maybe someone who uses =indirect.ext() can jump in with better instructions.

Kaby wrote:

I actually want to keep it as a string that turns into a formula so that when
I change the product name column, so does the filename and the linking. Is
this possible--if it makes sense?

"Kaby" wrote:

Terrific...this is good...I will work with this more, but I feel very close
to a solution. Thanks a bunch....I will let you know if I have any more
questions.

Kaby

"Dave Peterson" wrote:

This formula just creates a nice string.
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

You could convert it to values (edit|copy, edit|paste special|values) and then
you'd have this string:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Add an equal sign and it'll become a formula
='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Maybe you got the #value! error from the original workbook because the workbook
didn't exist on the C: drive???


Kaby wrote:

This is helpful...I feel like I am getting closer to programming that I want
to accomplish. When I type in this formula:
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

The cell value turns into this:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

and not the actual value found in cell c44. I am not sure why...but I think
my syntax to my formula is wrong.
Heck--doing it directly maybe better than indirectly...

Kaby

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

None from me.

Kaby wrote:

This formula:

=INDIRECT.EXT('H:\["&D100&".xls]BUYERSREQUEST'!C44)

keeps giving me a #value error...any obvious reasons....

"Dave Peterson" wrote:

You either have to do that string to formula yourself -- or use something else.

That something else is =indirect.ext().

Maybe someone who uses =indirect.ext() can jump in with better instructions.

Kaby wrote:

I actually want to keep it as a string that turns into a formula so that when
I change the product name column, so does the filename and the linking. Is
this possible--if it makes sense?

"Kaby" wrote:

Terrific...this is good...I will work with this more, but I feel very close
to a solution. Thanks a bunch....I will let you know if I have any more
questions.

Kaby

"Dave Peterson" wrote:

This formula just creates a nice string.
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

You could convert it to values (edit|copy, edit|paste special|values) and then
you'd have this string:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Add an equal sign and it'll become a formula
='H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

Maybe you got the #value! error from the original workbook because the workbook
didn't exist on the C: drive???


Kaby wrote:

This is helpful...I feel like I am getting closer to programming that I want
to accomplish. When I type in this formula:
="'H:\[" & D99 & ".xls]BUYERSREQUEST'!c44"

The cell value turns into this:
'H:\[Turbo Bug Vac_PRD.Rev1.xls]BUYERSREQUEST'!c44

and not the actual value found in cell c44. I am not sure why...but I think
my syntax to my formula is wrong.
Heck--doing it directly maybe better than indirectly...

Kaby

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson