Retrieve cell value
I want to retrieve a cell value from a closed workbook but the name of the
workbook varies depending the information entered in the active sheet. I used the concatenate function in cell B4 to identify the workbook name based on the information entered. For example: Test_June.xls In the product cell, I have =indirect(B4) but the function doesn't allow me to enter the column ($L$1) where the data is in the Test_June workbook. Please, help! |
Retrieve cell value
Open both workbooks (sending and receiving).
Select the sending cell L1 in the sending worksheet in the sending workbook. Edit|Copy Select the receiving cell in the receiving worksheet in the receiving workbook. Edit|paste special|paste link Close the sending workbook. Look at that formula (excel will modify it to include the path). That's the syntax you'll want to use. But... If that sending file is not open, then =indirect() returns an error. (saved from a previous post) The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Gisela wrote: I want to retrieve a cell value from a closed workbook but the name of the workbook varies depending the information entered in the active sheet. I used the concatenate function in cell B4 to identify the workbook name based on the information entered. For example: Test_June.xls In the product cell, I have =indirect(B4) but the function doesn't allow me to enter the column ($L$1) where the data is in the Test_June workbook. Please, help! -- Dave Peterson |
Retrieve cell value
How can I use a variable name file in the indirect? "Dave Peterson" wrote: Open both workbooks (sending and receiving). Select the sending cell L1 in the sending worksheet in the sending workbook. Edit|Copy Select the receiving cell in the receiving worksheet in the receiving workbook. Edit|paste special|paste link Close the sending workbook. Look at that formula (excel will modify it to include the path). That's the syntax you'll want to use. But... If that sending file is not open, then =indirect() returns an error. (saved from a previous post) The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Gisela wrote: I want to retrieve a cell value from a closed workbook but the name of the workbook varies depending the information entered in the active sheet. I used the concatenate function in cell B4 to identify the workbook name based on the information entered. For example: Test_June.xls In the product cell, I have =indirect(B4) but the function doesn't allow me to enter the column ($L$1) where the data is in the Test_June workbook. Please, help! -- Dave Peterson |
Retrieve cell value
If the file is closed, you'll get a ref error with =indirect().
You'll have to use =indirect.ext(). Build the formula without the =indirect.ext() function. Then share that formula. And share the cells that contain the name of the file, folder, sheet, range, ... and the values in those cells. The formula will change depending on how you've laid out that data. It could look like this: =indirect.ext("'C:\My Documents\excel\[master workbook.xls]" & A1 & "'!$A$1") or =indirect.ext("'" & x9 & "\[" & z99 & & ".xls]" & A1 & "'!$A$1") or lots of things... Gisela wrote: How can I use a variable name file in the indirect? "Dave Peterson" wrote: Open both workbooks (sending and receiving). Select the sending cell L1 in the sending worksheet in the sending workbook. Edit|Copy Select the receiving cell in the receiving worksheet in the receiving workbook. Edit|paste special|paste link Close the sending workbook. Look at that formula (excel will modify it to include the path). That's the syntax you'll want to use. But... If that sending file is not open, then =indirect() returns an error. (saved from a previous post) The function you'd want to use that's built into excel is =indirect(). But that function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. Gisela wrote: I want to retrieve a cell value from a closed workbook but the name of the workbook varies depending the information entered in the active sheet. I used the concatenate function in cell B4 to identify the workbook name based on the information entered. For example: Test_June.xls In the product cell, I have =indirect(B4) but the function doesn't allow me to enter the column ($L$1) where the data is in the Test_June workbook. Please, help! -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 05:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com