External Reference to Closed Workbook Define File Name in Current Workbook

[excellearner96]

I have built a template to partially autogenerate "recipes" for some automated equipment that reads parameters off of an excel sheet. Each recipe corresponds to a different design where I have to manually enter a bunch of different data points to generate the rest of the file. I am trying to automate the data loading from a different excel workbook which is output from our engineering department. I cannot use any macros per IT. So what I have right now is something like this function 

='C:\Users\telliott\Desktop\AutoloadTrial\[MOD1220014.xlsx]Data'!Main.InnerD.2

To autofill the cells I've been manually entering. All designs are saved in the same folder under saved under their model number (MOD1220014 for this example). There are ~50 cells with a function similar to this to pull data. Is there anyway to link the desired filename in this function to a cell in the current workbook of the template? So I can open the template type in the model number to a specific cell to tell these functions where to pull from. I tried this but it did not work:

='C:\Users\telliott\Desktop\AutoloadTrial\['$A$1'.xlsx]Data'!Main.InnerD.2

Then type MOD1220014 into cell A1 populating all of the functions. Is there anyway to do something like this?

Thanks,

[#2]

@excellearner96 

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?

[#3]

@hans Vegelaar
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.

[#4]

@excellearner96 

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 @Hans Vogelaar 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.

[#5]

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.