using cell contents in pathname

Since indirect won't work with closed files, couldn't u use indirect to make
the reference and then just copy/paste special values?

--
Don Guillett
SalesAid Software

"Howie" wrote in message
...
Some additional clarification may be necessary
If I actually enter
='\\path\to\[7.01AD.xls]BS'!$Q$81 in B1
it works fine, the worksheet updates when I open it.
What I'm trying to do is copy the formula down the rows changing the name
of
the workbook in each row to match the contents of column A.
Thanks
Howie Goldman


"Dave Peterson" wrote in message
...
I think the worksheet function you'd want to use is =indirect(). But
the
bad
news is =indirect() won't work with closed files. And I'm guessing you
won't
open all the possible workbooks so that the one you want will be open.

But John Walkenbach has some code that will allow a macro (not a
worksheet
function) retrieve values from a closed workbook.

http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

But Harlan Grove posted a function that opens the other workbook and
retrieves
the value from a separate instance of excel:


http://google.com/groups?threadm=oZx...wsra nger.com

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Howie wrote:

Hi,

I'm trying to build a formula that will retrieve the value of a cell
in
another workbook using the contents of a cell on the current sheet as
the
filename of the other workbook.
as an example;

cell A1 has 7.01AD in it. cell A2 has 7.01AB, cell A3 has random.name
in
it

in cell B1 I want the value of cell Q81 from sheet BS in workbook
7.01AD.xls
in cell B2 I want the value of cell Q81 from sheet BS in workbook
7.01AB.xls
in cell B3 I want the value of cell Q81 from sheet BS in workbook
random.name.xls

I can of course open the workbook and point to the cell. That gives me
='\\path\to\[7.01AD.xls]BS'!$Q$81
I'm trying to avoid having to open and point to the cell in each
worksheet.

I tried
='C:\path\to\[&A1&]BS'!$Q$81
doesn't work, similarly
='C:\path\to\[&text(A1)&]BS'!$Q$81
doesn't work.
I get an update value dialog box looking for the file I want to open.

Any help?

Thanks and Happy New Year

Howie Goldman

--

Dave Peterson

Hi,

I'm trying to build a formula that will retrieve the value of a cell in
another workbook using the contents of a cell on the current sheet as the
filename of the other workbook.
as an example;

cell A1 has 7.01AD in it. cell A2 has 7.01AB, cell A3 has random.name in it

in cell B1 I want the value of cell Q81 from sheet BS in workbook 7.01AD.xls
in cell B2 I want the value of cell Q81 from sheet BS in workbook 7.01AB.xls
in cell B3 I want the value of cell Q81 from sheet BS in workbook
random.name.xls

I can of course open the workbook and point to the cell. That gives me
='\\path\to\[7.01AD.xls]BS'!$Q$81
I'm trying to avoid having to open and point to the cell in each worksheet.

I tried
='C:\path\to\[&A1&]BS'!$Q$81
doesn't work, similarly
='C:\path\to\[&text(A1)&]BS'!$Q$81
doesn't work.
I get an update value dialog box looking for the file I want to open.

Any help?

Thanks and Happy New Year

Howie Goldman

I think the worksheet function you'd want to use is =indirect(). But the bad
news is =indirect() won't work with closed files. And I'm guessing you won't
open all the possible workbooks so that the one you want will be open.

But John Walkenbach has some code that will allow a macro (not a worksheet
function) retrieve values from a closed workbook.

http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

But Harlan Grove posted a function that opens the other workbook and retrieves
the value from a separate instance of excel:
http://google.com/groups?threadm=oZx...wsra nger.com

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Howie wrote:

Hi,

I'm trying to build a formula that will retrieve the value of a cell in
another workbook using the contents of a cell on the current sheet as the
filename of the other workbook.
as an example;

cell A1 has 7.01AD in it. cell A2 has 7.01AB, cell A3 has random.name in it

in cell B1 I want the value of cell Q81 from sheet BS in workbook 7.01AD.xls
in cell B2 I want the value of cell Q81 from sheet BS in workbook 7.01AB.xls
in cell B3 I want the value of cell Q81 from sheet BS in workbook
random.name.xls

I can of course open the workbook and point to the cell. That gives me
='\\path\to\[7.01AD.xls]BS'!$Q$81
I'm trying to avoid having to open and point to the cell in each worksheet.

I tried
='C:\path\to\[&A1&]BS'!$Q$81
doesn't work, similarly
='C:\path\to\[&text(A1)&]BS'!$Q$81
doesn't work.
I get an update value dialog box looking for the file I want to open.

Any help?

Thanks and Happy New Year

Howie Goldman

--

Dave Peterson

Some additional clarification may be necessary
If I actually enter
='\\path\to\[7.01AD.xls]BS'!$Q$81 in B1
it works fine, the worksheet updates when I open it.
What I'm trying to do is copy the formula down the rows changing the name of
the workbook in each row to match the contents of column A.
Thanks
Howie Goldman


"Dave Peterson" wrote in message
...
I think the worksheet function you'd want to use is =indirect(). But the
bad
news is =indirect() won't work with closed files. And I'm guessing you
won't
open all the possible workbooks so that the one you want will be open.

But John Walkenbach has some code that will allow a macro (not a worksheet
function) retrieve values from a closed workbook.

http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

But Harlan Grove posted a function that opens the other workbook and
retrieves
the value from a separate instance of excel:

http://google.com/groups?threadm=oZx...wsra nger.com

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Howie wrote:

Hi,

I'm trying to build a formula that will retrieve the value of a cell in
another workbook using the contents of a cell on the current sheet as
the
filename of the other workbook.
as an example;

cell A1 has 7.01AD in it. cell A2 has 7.01AB, cell A3 has random.name in
it

in cell B1 I want the value of cell Q81 from sheet BS in workbook
7.01AD.xls
in cell B2 I want the value of cell Q81 from sheet BS in workbook
7.01AB.xls
in cell B3 I want the value of cell Q81 from sheet BS in workbook
random.name.xls

I can of course open the workbook and point to the cell. That gives me
='\\path\to\[7.01AD.xls]BS'!$Q$81
I'm trying to avoid having to open and point to the cell in each
worksheet.

I tried
='C:\path\to\[&A1&]BS'!$Q$81
doesn't work, similarly
='C:\path\to\[&text(A1)&]BS'!$Q$81
doesn't work.
I get an update value dialog box looking for the file I want to open.

Any help?

Thanks and Happy New Year

Howie Goldman

--

Dave Peterson

"Don Guillett" wrote...
Since indirect won't work with closed files, couldn't u use indirect to
make
the reference and then just copy/paste special values?
....
"Howie" wrote in message
....
What I'm trying to do is copy the formula down the rows changing the name
of the workbook in each row to match the contents of column A.
....

See the linked article below for an approach that doesn't require INDIRECT.

http://www.google.com/groups?selm=51...wsranger. com

Thanks for the prompt reply.
I won't have a chance to try it till Friday.
My substitution is in the middle of the text string, I'm thinking that might
be a problem
I still don't get why I can't just use the formula I wrote
='C:\path\to\[&A1&.xls]BS'!$Q$81
(in the original post I left out the .xls)
Excel can't seem to resolve the cell reference in the square brackets and
goes looking for a file called "&A1&.xls"
Howie

"Harlan Grove" wrote in message
...
"Don Guillett" wrote...
Since indirect won't work with closed files, couldn't u use indirect to
make
the reference and then just copy/paste special values?
...
"Howie" wrote in message
...
What I'm trying to do is copy the formula down the rows changing the
name
of the workbook in each row to match the contents of column A.
...

See the linked article below for an approach that doesn't require
INDIRECT.


http://www.google.com/groups?selm=51...wsranger. com

Yes Yes Yes
I put the beginning of the path in one cell, the closing bracket and the
sheet and cell reference in another cell and concatenated the three cells.
then I did the paste special values and the replace = with = and it works. I
don't quite understand why the last steps are necessary but I'll work that
out later. Thanks Thanks thanks
Howie


"Howie" wrote in message
...
Thanks for the prompt reply.
I won't have a chance to try it till Friday.
My substitution is in the middle of the text string, I'm thinking that
might
be a problem
I still don't get why I can't just use the formula I wrote
='C:\path\to\[&A1&.xls]BS'!$Q$81
(in the original post I left out the .xls)
Excel can't seem to resolve the cell reference in the square brackets and
goes looking for a file called "&A1&.xls"
Howie

"Harlan Grove" wrote in message
...
"Don Guillett" wrote...
Since indirect won't work with closed files, couldn't u use indirect to
make
the reference and then just copy/paste special values?
...
"Howie" wrote in message
...
What I'm trying to do is copy the formula down the rows changing the
name
of the workbook in each row to match the contents of column A.
...

See the linked article below for an approach that doesn't require
INDIRECT.



http://www.google.com/groups?selm=51...wsranger. com

Ok, I looked further and understand the paste special values part.
thanks again.
Howie

"Howie" wrote in message
...
Yes Yes Yes
I put the beginning of the path in one cell, the closing bracket and the
sheet and cell reference in another cell and concatenated the three cells.
then I did the paste special values and the replace = with = and it works.
I
don't quite understand why the last steps are necessary but I'll work that
out later. Thanks Thanks thanks
Howie


"Howie" wrote in message
...
Thanks for the prompt reply.
I won't have a chance to try it till Friday.
My substitution is in the middle of the text string, I'm thinking that
might
be a problem
I still don't get why I can't just use the formula I wrote
='C:\path\to\[&A1&.xls]BS'!$Q$81
(in the original post I left out the .xls)
Excel can't seem to resolve the cell reference in the square brackets
and
goes looking for a file called "&A1&.xls"
Howie

"Harlan Grove" wrote in message
...
"Don Guillett" wrote...
Since indirect won't work with closed files, couldn't u use indirect
to
make
the reference and then just copy/paste special values?
...
"Howie" wrote in message
...
What I'm trying to do is copy the formula down the rows changing the
name
of the workbook in each row to match the contents of column A.
...

See the linked article below for an approach that doesn't require
INDIRECT.




http://www.google.com/groups?selm=51...wsranger. com