Referencing value in a closed Excel workbook using

Question

I want to refer to a cell value in another closed workbook with a formula (not VBA!). The Sheet name is stored as a variable (in the following example, C13 is "Sheet2").

If the other file is open, then following works:

=INDIRECT("[myExcelFile.xlsm]" & C13 & "!$A$1")

If the file is closed, the above formula doesn't work, as there is no absolute path given. But I got it work with following (give attention to ' instead of ":

='C:\data\[myExcelFile.xlsm]Sheet2'!$A$1

Now I want to replace the hardcoded "Sheet2" with a dynamic referenced value, means with C13 (as seen in the first code snippet).

Does anybody know a solution without using VBA or other libraries?

Answer 1

In Excel 2016 at least, you can use INDIRECT with a full path reference; the entire reference (including sheet name) needs to be enclosed by ' characters.

So this should work for you:

= INDIRECT("'C:\data\[myExcelFile.xlsm]" & C13 & "'!$A$1")

Note the closing ' in the last string (ie '!$A$1 surrounded by "")