How to hold link in indirect formula? Help!

[Big Bruiser]

Guys, please help with the following formula. I have a file name inside of an indirect formula which means I have to have quotes around it. The problem is that when I rename the linked file, using edit links does not work because the filename is text. Is there a way to use the indirect formula and have the link update to the new file when I use edit link? thanks!

Here is the formula. In the formula below, it is the first link (Detailed Expense Allocation - Standard 2011-08-19.xlsm) that I want to be able to change using edit link.

=IFERROR(SUMIFS(INDIRECT("'[Detailed Expense Allocation - Standard 2011-08-19.xlsm]"&"FTE Data'!FTEDataTable["&$F10&"]"),'[Detailed Expense Allocation - Standard 2011-08-19.xlsm]FTE Data'!$V$16:$V$4039,$E$8&$C10),0)

 

[Andrew Poulsom]

As you have noticed that won't be recognised as a link. Why the need for INDIRECT?

 

[Big Bruiser]

referencing a table column with dynamic columns. In the formula, my table is "FTADataTable" and you can see that I have a cell reference in the [] to dynamically refer to a table column.

 

[Big Bruiser]

I changed my formula to an offset formula to dynamically refer to a column and it works now!

=SUMIFS(OFFSET('Detailed Expense Allocation - Standard 2011-08-19.xlsm'!FTEDataTable[Employees],,MATCH($F10,'Detailed Expense Allocation - Standard 2011-08-19.xlsm'!FTEDataTable[[#Headers],[Employees]:[Total FTEs]],0)-1),'Detailed Expense Allocation - Standard 2011-08-19.xlsm'!FTEDataTable[Base Case - Base Year Match (Org Lvl A,B,C & RI)],$E$8&$C10)