If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
using cell contents in pathname
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 |
#3
|
|||
|
|||
using cell contents in pathname
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 |
#4
|
|||
|
|||
using cell contents in pathname
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 |
#5
|
|||
|
|||
using cell contents in pathname
"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 |
#6
|
|||
|
|||
using cell contents in pathname
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 |
#7
|
|||
|
|||
using cell contents in pathname
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 |
#8
|
|||
|
|||
using cell contents in pathname
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 |
Thread Tools | |
Display Modes | |
|
|