Reference value in closed Excel workbook using INDIRECT?

Question

I want to refer to the value of a cell in another closed book with a formula (not VBA!). The sheet name is saved as a variable (in the following example, C13 is “Sheet2”).

If another file is open, then the following works:

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

If the file is closed, the above formula does not work because the absolute path is not specified. But I got the job with the following (note the “instead”:

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

Now I want to replace the hard-coded "Sheet2" with a dynamic reference value, that is, with C13 (as seen from the first code snippet).

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

Answer 1

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

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

2. Let's imagine that you created this formula in cell B2 Sheet1 , and you want the formula to be evaluated in column c .

3. Now go to the Formulas tab and select "Define Name". Give it the name myResult (or whatever you choose) and in myResult refers to "write =evaluate(Sheet1!$B2) (pay attention to $ )

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

Answer 2

Check INDEX function:

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

Answer 3

= INDIRECT ("'C: \ Data" SheetName ["&; A8 and amplifier]! $ G9")

where A8 contains myExcelFile.xlsm

and G9 contains valuable data from your source book.

Answer 4

I also searched for the answer to cell references in a closed book. Below is a link to the solution (correct formula) below. I tried this in my current project (referring to a single cell and an array of cells) and it works well without errors. Hope this helps you.

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

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

If you want to manually select a sheet for reference, use this formula

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

After applying this formula, you will get the Select Sheet dialog box, select the worksheet and click OK. Then a specific cell of this sheet will be immediately referenced.

Answer 5

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

So this should work for you:

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

Note the close of ' on the last line (i.e. '!$A$1 surrounded by "" )

Answer 6

The problem is that the link to the closed file works with the index (but not with the index (indirect (

It seems to me that this is the problem of programming an index function. I solved it with a sentence line

 C2=sheetname if(c2=Sheet1,index(sheet1....),if(C2="Sheet2",index(sheet2.... 

I did it in five sheets, this is a long formula, but it does what I need.

Answer 7

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

 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 does not require opening a workbook with recommendations - Excel will open it yourself (but it will be hidden).

Answer 8

Ok

Here is the dinosaur method for Office 2010.

Enter the full address you want to use using concatenate (the & method to combine text).

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

= "=" & "'\ FULL NETWORK ADDRESS, including [Table Name]" & W3 & "'! $ W4"

W3 is a dynamic link to what I use, W4 is the cell I want to get from the sheet.

Then start the macro session. Copy the cell and paste it into another. I put it in a merged cell, and this gave me the classic "Same Size" error. But one thing she did was paste the text from my concatenate (including the extra "=").

Copy everything you did for this. Then go to each inserted cell, select the text and just press enter. It updates it to an active direct link.

Once you're done, put the cursor somewhere beautiful and stop the macro. Assign it to the button and everything will be ready.

This is a bit of PITA to do this for the first time, but as soon as you have done this, you just made a square snap suitable that made a round hole.