Reference Data File Paths Change When I Paste Template in New Location

[Justin_Grunigen]

Good Morning,

I have searched and am not finding a solution to my problem. Please point me in the right direction if there is a thread for this all ready I'm overlooking. 

I have a template that is designed to pull from a table of data. The table is a separate file that gets updated constantly. The location and name of the table does not change for each project.

I am trying to get my template to pull from this table with an index & match formula. It works great, however when I copy and paste the template into a new location the file path to the table changes. WHY? I want to reference the same table, same name, same location - regardless of where I copy and paste the template file. Why is that so difficult... 

I do not want users to have to have both files open to update it either so INDIRECT will not work. 

I prefer not to overcomplicate it with VBA either but I'm running out of options. 

Thank you for any help you can provide.

[#2 · Accepted solution]

By design, paths in Excel formula links are relative. So if you copy a file elsewhere, Excel will try to find the linked file *relative* to the new location.
INDEX MATCH against an external file is a relatively slow process. Given that you stated that you want the path to be absolute, why not add a tab to your file and use Data, Get Data, From File, From Workbook to import the entire table. You can then set that connection to update e.g. on file open. Point your INDEX/MATCH formulas to the new table in your current file.

[#3]

THANK YOU! This thought also occurred to me after posting this. I was in the middle of performing those edits to test. Initial testing just worked!!