Linking many source files to a single data table

[afarcet]

Dear Excel gurus,

I have a customer whose Finance Controller is spending hours (if not days) every month to produce a Balanced Scorecard.

He has set up links from the scorecard to over 20 external files. There are two challenges as I see it:

1) The names of those files change monthly (i.e. SalesJan becomes SalesFeb, etc.). At the moment, he's trawling through his formulas and changing the names of the links manually.

2) The links have to feed a different column each month.

In general, I think the best way forward would be to feed all this source data into a pivot table from which we could create whatever reports we want. So the question is, what's the best way to link a data table to many different cells in many different files whose names frequently change?

Many thanks in advance.

Alex

 

[PATSYS]

For #1, he or you do not have to change the individual formula with link manually. You can just go to EDIT menu, LINKS, and change the source file to the new file. This will point the link to the new file.

As for #2, I guess the you can highlight the entire range, then FIND e.g. "!E" and REPLACE with e.g. "!F". Include the ! in order to make the search unique otherwise all the Es will be replaced.

 

[PCL]

You can select the cells where formulaes with file name are and make a FIND / REPLACE.