Changing the link in an external reference

[Paul Frewer]

Hi I have a number of liked spreadsheets which need the links updating every year. I assumed I'd be able to change the year reference to the correct year and it would reflect the relevant data but it's apparently not that easy. the existing formula is =SUM('[Total Daily Output Figures.xlsx]2017 Daily Output Figures'!$B$12:$B$18) I was hoping to just change the year (2017) to 2018 and it would then reference the newly created 2018 Daily Output Figures'!$B$12:$B$18 and assumed it would work but it appears this breaks the link altogether. I've tried editing the link but that doesn't seem to work either. I really don't want to have to manually reset all the links at the start of every year so I'm hoping there's an easy way of achieving and easy yearly transition.

[#2]

Excel stacks all those references to sheets in external workbooks to one link per external workbook, so links are managed is "per workbook", not "per worksheet". If all that changes is a worksheet name in an external workbook, perhaps it is better to work with a named range *in the external workbook* to which you point in your reporting workbook (the one with the link). If the year changes, you open the SOURCE and change the reference of that name. Alternatively, do a search and replace in your workbook, repacing 2017 Daily Output Figures with 2018 Daily Output Figures.