Dec 08 2022 10:49 AM
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,
Dec 08 2022 12:24 PM
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?
Dec 13 2022 06:16 AM
Dec 13 2022 06:30 AM - edited Dec 13 2022 06:32 AM
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.
Dec 13 2022 07:07 AM