Creating a template file - using a cell to hold the link to an external data file for all formulas in the template file?

[TheMegaSage]

I'm working on a file for my department, and it's possible that other departments will want to use it as well, so I'm trying to create the formulas so they aren't hardcoded and can link to different data files.

Essentially my formulas, as mentioned, all link to a data file. The intent is to run this "data dump file" once a month and just save it to a sharepoint folder. In my template file, I have formulas that reference this data file.

Example: =IF(ISTEXT('https://xxx.sharepoint.com/sites/IT/Shared Documents/Financials/2021/Data files/[IT 2021 YTD.xlsx]Data'!$A2),'https://xxx.sharepoint.com/sites/IT/Shared Documents/Financials/2021/Data files/[IT 2021 YTD.xlsx]Data'!A2,"")

Because it's possible different departments will eventually be using this template, I'm wondering if there's a way to create a cell in the template file that references the data file. I suppose I could just do a Find/Replace on https://xxx.sharepoint.com/sites/IT/Shared Documents/Financials/2021/Data files/[IT 2021 YTD.xlsx] and enter the link to the new folder, but that's not ... elegant.

I want to create a Setup tab and have a cell that contains the direct link to the data dump file. Then in my formulas, I would just reference that cell instead of direct to the data file. Example:

=IF(ISTEXT(CONCAT(Setup!$B$1,"$A2")),CONCAT(Setup!$B$1,"$A2"),"")

The Setup!$B$1 cell contains the direct link to the data file.

Is this possible?

 

[severynm]

If you use the excel desktop app, you can use =CELL("filename") to return the http file path of the file. You could use a substring of that result combined with the Indirect function to create a link. Note that for whatever reason, this function does not work in the web app version of excel.