Referencing value in a closed Excel workbook using INDIRECT?

Solution 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!

Solution 2:

I too was looking for the answer to referencing cells in a closed workbook. Here is the link to the solution (correct formula) below. I have tried it on my current project (referencing a single cell and an array of cells) and it works well with no errors. I hope it helps you.

https://www.extendoffice.com/documents/excel/4226-excel-reference-unopened-file.html

In the formula, E:\Excel file\ is the full file path of the unopened workbook, test.xlsx is the name of the workbook, Sheet2 is the sheet name which contains the cell value you need to reference from, and A:A,2,1 means the cell A2 will be referenced in the closed workbook. You can change them based on your needs.

If you want to manually select a worksheet to reference, please use this formula

=INDEX('E:\Excel file\[test.xlsx]sheetname'!A:A,2,1)

After applying this formula, you will get a Select Sheet dialog box, please select a worksheet and then click the OK button. Then the certain cell value of this worksheet will be referenced immediately.