Looking for a way of using a text cell value in a link formula linkiing multiple file

S

Sackie01

New member
Joined
Apr 29, 2014
Messages
6
Reaction score
0
Points
0
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.
 
Sackie01 said:
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.
Click to expand...

Hi
Try this formula, adjust as necessary.
Good luck.
Tony
 
Try:

=INDIRECT("'"&A2&"'!A10")

Note: That you cannot use this to reference a closed workbook, though. The workbook must be open.
 
NBVC said:
Try:

=INDIRECT("'"&A2&"'!A10")

Note: That you cannot use this to reference a closed workbook, though. The workbook must be open.
Click to expand...

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
 
You must log in or register to reply here.
Back
Top