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

If you know the number of sheet you want to reference you can use below function to find out the name. Than you can use it in INDIRECT funcion.

Public Function GETSHEETNAME(address As String, Optional SheetNumber As Integer = 1) As String

    Set WS = GetObject(address).Worksheets
    GETSHEETNAME = WS(SheetNumber).Name

End Function

This solution doesn't require referenced workbook to be open - Excel gonna open it by itself (but it's gonna be hidden).

Answer 2

Check INDEX Function:

=INDEX('C:\path\[file.xlsm]Sheet1'!A10:B20;1;1)

Answer 3

=INDIRECT("'C:\Data["&A8&"]SheetNAME'!$G9")

where A8 contains myExcelFile.xlsm

and G9 contains your source workbook precious data.

Answer 4

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!

Answer 5

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.

Answer 6

OK,

Here's a dinosaur method for you on Office 2010.

Write the full address you want using concatenate (the "&" method of combining text).

Do this for all the addresses you need. It should look like:

="="&"'\FULL NETWORK ADDRESS including [Spreadsheet Name]"&W3&"'!$w4"

The W3 is a dynamic reference to what sheet I am using, the W4 is the cell I want to get from the sheet.

Once you have this, start up a macro recording session. Copy the cell and paste it into another. I pasted it into a merged cell and it gave me the classic "Same size" error. But one thing it did was paste the resulting text from my concatenate (including that extra "=").

Copy over however many you did this for. Then, go into each pasted cell, select he text and just hit enter. It updates it to an active direct reference.

Once you have finished, put the cursor somewhere nice and stop the macro. Assign it to a button and you are done.

It is a bit of a PITA to do this the first time, but once you have done it, you have just made the square peg fit that daamned round hole.