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

[Sackie01]

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.

[OTPM]

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.

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

[Sackie01]

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

Hi Tony, think I need the formula though....

[NBVC]

Try:

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

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

[Sackie01]

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.

[NBVC]

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