I am attempting to use a value in a cell in one file (value in A2{cell contains 'Wk 38 May 10, 2014'}) and the value in A2 is the name of a worksheet in another file, so I want to create a link formula that will use the value in A2 to identify the worksheet to reference to within the link formula.
Example='[Book2]Wk 38 May 10, 2014'!$A$10, would like to replace the 'Wk 38 May 10, 2014' with the value that is in cell A2 of the worksheet that contains the formula.
Try:
=INDIRECT("'"&A2&"'!A10")
Note: That you cannot use this to reference a closed workbook, though. The workbook must be open.
Actually I am looking for a formula that would work with a close file.
I have been testing the CELL function but I cannot seem to get this to work inside of a formula.
='C:\My Documents\[Test file.xlsx]CELL("contents",A2)'!$A$10 does NOT work but I cannot figure out my error.
You can't "indirectly" reference a closed workbook with Excel's base functions.
You will need to use a UDF.
Here is a popular one called: PULL from Harlan Grove
Bookmarks