I have an (Conversion) document in the cloud which is getting its data from a different open workbook. The open workbook has always a fixed name (Template.xlsm). Now when I open the conversion document on a different pc, Excel automatically adds whole file path of where it originally been created in the formula.
How can I stop excel from adding a file path in the formula and let it always look for the open workbook with that specific name, no matter on which system the conversion document is?
Current formula is ='[Template.xlsm]Data'!A1
First, you cannot stop Excel from adding the full file path. Let me explain why.
When you reference an external workbook like that, the reference only works if the external file is open in the same Excel instance. As soon as you close the "Template.xlsm" file, Excel will reference the file with its full path. The full path is required, so Excel can find the file on the file system. Since the file is no longer open, Excel needs the file path to find the file.
When you open the conversion document on another computer, the stored file path to the template file will be used.
Unless the Template file is open in Excel.
So, here is what you need to do to make this work on another computer:
Open the file called "Template.xlsm" on the other computer first, and when that template file is open, only then open the conversion file (which has a reference to the template file). Excel will dismiss the file path to the original template file and refer to the currently open template file.