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
|
|||
|
|||
How to Build an External Link Using Data from Cells
I want to import data in a cell from an external
spreadsheet located at 'C:\Data\[MyXLS.xls]MySheet!$H$5. I want to build it using cells from the current worksheet such as: A1 = C:\Data A2 = MyXLS.xls A3 = MySheet In cell A4, I want to build the file location string and have the value displayed. For example, in A4 if I enter something similar to "='C:\Data\[MyXLS.xls]MySheet'!$H$5" and if H5 contained 25, then 25 would display in A4. This works fine, but when I try to build the formula using values from cells A1, A2 and A3 it doesn't work. Thanks in advance for any suggestions. |
#2
|
|||
|
|||
How to Build an External Link Using Data from Cells
Hi
you have to use the function INDIRECT. So in your example enter the following =INDIRECT("'" & A1 & "\[" & A2 & "]" & A3 & "'!$H$5") BUT this will work only if the second sheet is open. INDIRECT will not work for closed files!. If you have to reference closed files based on other cells, you may have a look at the free add-in Morefunc.xll at http://longre.free.fr/english/ This includes a function INDIRECT.EXT which will overcome Excels restrictions regarding closed files. Another note: INDIRECT.EXT won't work in combination with functions like VLOOKUP and closed files. But if you only want to import data, INDIRECT.EXT should work for you HTH Frank wrote: I want to import data in a cell from an external spreadsheet located at 'C:\Data\[MyXLS.xls]MySheet!$H$5. I want to build it using cells from the current worksheet such as: A1 = C:\Data A2 = MyXLS.xls A3 = MySheet In cell A4, I want to build the file location string and have the value displayed. For example, in A4 if I enter something similar to "='C:\Data\[MyXLS.xls]MySheet'!$H$5" and if H5 contained 25, then 25 would display in A4. This works fine, but when I try to build the formula using values from cells A1, A2 and A3 it doesn't work. Thanks in advance for any suggestions. |
#3
|
|||
|
|||
How to Build an External Link Using Data from Cells
"Frank Kabel" wrote...
you have to use the function INDIRECT. So in your example enter the following =INDIRECT("'" & A1 & "\[" & A2 & "]" & A3 & "'!$H$5") BUT this will work only if the second sheet is open. INDIRECT will not work for closed files!. 'sheet'? The other workbook must be open. If you have to reference closed files based on other cells, you may have a look at the free add-in Morefunc.xll at http://longre.free.fr/english/ This includes a function INDIRECT.EXT which will overcome Excels restrictions regarding closed files. Another note: INDIRECT.EXT won't work in combination with functions like VLOOKUP and closed files. But if you only want to import data, INDIRECT.EXT should work for you INDIRECT.EXT works just fine with VLOOKUP. None of the arguments to VLOOKUP need to be ranges - any and all could be arrays (though array 4th arguments are weird). There are few worksheet functions that require only ranges and never arrays. Some of them are OFFSET, SUMIF, COUNTIF and RANK. There may be others, but I'm too lazy to give an exhaustive list. -- To top-post is human, to bottom-post and snip is sublime. |
Thread Tools | |
Display Modes | |
|
|