Referencing value in a closed Excel workbook using INDIRECT?

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

There is definitively no way to do this with standard formulas. However, a crazy sort of answer can be found here. It still avoids VBA, and it will allow you to get your result dynamically.

1. First, make the formula that will generate your formula, but don't add the = at the beginning!

2. Let us pretend that you have created this formula in cell B2 of Sheet1, and you would like the formula to be evaluated in column c.

3. Now, go to the Formulas tab, and choose "Define Name". Give it the name myResult (or whatever you choose), and under Refers To, write =evaluate(Sheet1!$B2) (note the $)

4. Finally, go to C2, and write =myResult. Drag down, and... voila!