using cell contents in pathname

  • Thread starter Thread starter Don Guillett
  • Start date Start date
D

Don Guillett

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
(e-mail address removed)
Howie said:
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 said:
I think the worksheet function you'd want to use is =indirect(). But
Click to expand...
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:
Click to expand...
http://google.com/[email protected]
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
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
Click to expand...
 
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 said:
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/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
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
Click to expand...
Click to expand...
 
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
 
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 said:
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 said:
...

See the linked article below for an approach that doesn't require INDIRECT.
Click to expand...
http://www.google.com/[email protected]
Click to expand...
 
Ok, I looked further and understand the paste special values part.
thanks again.
Howie

Howie said:
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 said:
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 said:
Since indirect won't work with closed files, couldn't u use indirect to
make
the reference and then just copy/paste special values?
...
...
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.
Click to expand...
Click to expand...
http://www.google.com/[email protected]
Click to expand...
 
Back
Top