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    amp  C13  amp     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

User · Answer

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.

First, make the formula that will generate your formula, but don't add the = at the beginning!
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.
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 $)
Finally, go to C2, and write =myResult. Drag down, and... voila!

User · Answer

INDIRECT   C  Data   amp A8 amp   SheetNAME   G9    where A8 contains myExcelFile xlsm  and G9 contains your source workbook precious data

User · Answer

The problem is that a link to a closed file works with index  but not with index indirect   It seems to me that it is a programming issue of the index function  I solved it with a if clause row  C2 sheetname if c2 Sheet1 index sheet1      if C2  Sheet2  index sheet2       I did it over five sheets  it s a long formula  but does what I need

User · Answer

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    amp  C13  amp      A 1     Note the closing   in the last string  ie    A 1 surrounded by

User · Answer

OK   Here s a dinosaur method for you on Office 2010   Write the full address you want using concatenate  the   amp   method of combining text    Do this for all the addresses you need  It should look like        amp    FULL NETWORK ADDRESS including  Spreadsheet Name   amp W3 amp     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

User · Answer

This seems to work with closed file  add a pivot table  rows  tabular layout  no subtotals  no grand totals  of the source to the current workbook  then reference all you want from that pivot table  INDIRECT  LOOKUPs

User · Answer

Check INDEX Function    INDEX  C  path  file xlsm Sheet1  A10 B20 1 1

User · Answer

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

User · Answer

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

[excel] Referencing value in a closed Excel workbook using INDIRECT?

Examples related to excel

Examples related to excel-formula