I am trying to link two workbooks but am having a bit of trouble.

I have done my homework on the basics and can link two workbooks but cannot do what I am trying to achieve. Wondering if someone can help!

I think the problem is the way the sheets are created and used. Both the source and destination workbooks are always new. Imagine a firm with 100 clients and each client has a workbook that is the same in format but contains different data for each client for that year. When next year rolls around a new destination workbook is created for each client and it has to start life as an Excel Template. This means the links must be in the excel template that then spawns them into the new destination workbook Once the new destination workbook is created it is moved into the client’s directory. Doing this naturally breaks the links embedded by the template. That’s fine but I notice that when the destination workbook is spawned from a template the “Edit link” feature in “Data” does not let you re attach the link by pointing to last year excel file (now source) file. This only seems to happen if the links are spawned from a template. If the destination workbook starts life as a normal excel file the links can be re attached with ease.

Is there a way around this or is it the case that broken links cannot be re-linked if the links are created from a template?