Forum Discussion
External Reference to Closed Workbook Define File Name in Current Workbook
The INDIRECT function can convert a text string to a cell reference, but it only works with an external workbook if that workbook is open in Excel. It will return #REF! if the external workbook is closed.
With MOD1220014 in A1, you could use
=INDIRECT("'["&$A$1&".xlsx]Data'!Main.InnerD.2")
What is Main.InnerD.2 by the way?
Thank you for the reply. I don't think that this really solves what I am trying to do, or if it does I cannot get it to work. I am trying to use a cell reference to call a text string inside of a filepath so that the filename can be typed into a cell to complete the function.
The function as is retrieves all the data I need but I have to go in and change file name in each function for it to do that. My goal is to use the cell reference to replace the file name with whatever text is in that cell.
Main.InnerD.2 is the name of the cell being referenced in the external worksheet that is output by engineering dept. They name the output cells so they are easier to reference as they are not dependent on location within the worksheet.
I believe INDIRECT is relevant to your request. INDIRECT can essentially convert a text reference to a cell address, sheet reference, named range, etc.
For example, without INDIRECT the contents of B1 are treated as literal text:
With INDIRECT, I'm able to pull the value from Sheet2, cell A1.
These are basic examples but show INDIRECT's purpose. This relates to your request because INDIRECT would be the function to use if the source was not a closed workbook, as HansVogelaar mentioned above.
For a workaround, you might consider using PowerQuery to pull from the external workbooks and load data to the sheet. The issue with maintaining linked workbooks via formula is there are quite a few functions that won't calculate if the source is closed.
- I see, I guess I was using it incorrectly. I'll see if I can get that to work, everyone may just have to live with opening the reference workbook. Thank you for the suggestion on PowerQuery as well, I'll be looking deeper into that.
