External links: Change source simplification?

[Eldacan]

Hi,

I have the following issue:

I have one file in which there are many cells with sums of VLOOKUPS in other files (i.e. A1=VLOOKUP(FILE_1)+VLOOKUP(FILE_2)). This file is used to create monthly reports, so every month there is a new FILE_1 and FILE_2 with a new name and a new summary file with these sums and operations involving the new FILE_1 and FILE_2.

After doing this process for the first month, what I did for the following one was to copy the summary file, rename it and when I went to Data->Edit Links and selected the option to change the source of this FILE_1 to the new one from this month, it starts asking me, for EVERY cell in which there's a formula involving FILE_1 and any other file, to select the source for those files too. By this I mean that if I have 4 files, instead of changing the source for each one of them for the new one (4 changes) and that being valid for all the document, I have to do it for each cell, so if for example I have 10 cells with formulas involving those 4 files, I need to make 40 sources change. And I have dozens of formulas, not just 10!

So I wanted to know if there is some way to avoid this happening and just changing once each source or in general a smarter way to do this.

Thank you very much for your help.

[JeffreyWeir]

Hi Eldacan. Couple of thoughts:

1. I avoid doing VLOOKUPS across files. My preference instead is to put all the data in one table in one file, and use a pivottable to do my aggregations.
2. If this will make the file too big, I'll maybe use a bit of VBA and SQL (a database language that Excel understands) to mash up the totals I need directly from the source files. THis avoids the use of any hard-coded formulas whatsoever. For examples of this approach see http://blog.contextures.com/archives...ultiple-files/ and http://blog.contextures.com/archives...n-pivot-table/
3. I'm not really familiar with Data->Edit Links. But maybe instead of using Data->Edit Links you could instead do a global find and replace each month, replacing the old FILE_1 and FILE_2 address with the new FILE_1 and FILE_2 addresses.

[Eldacan]

Thank you for your asnwer JeffreyWeir.

1. Given the nature of the reports they have to be separate files just for conceptual reasons.

2. I don't know VBA or SQL but I'll try what you suggest. If not for this specific case, as in these files there is not just one big nice table as the one shown in the example of your links but many small tables per sheet that were designed without great thoughts on their automation and have to remain as they are, it will be useful in other situations. Maybe I have to create hundreds of pivot tables, though. I'll give it a look.

3. That produces the same error unluckily.

[Eldacan]

OK, I have to doublepost as it won't allow me to edit my post...

Regarding point 2., I can't do that in this case as I don't have a big table with IDs and so on as the product lists in the example but small tables with information about financial statements, so for example one table has as row names some elements of a balance sheet and as column names periods of time. That's why I was doing VLOOKUPS to say things like "Find the value for Accruals for June 2013 in this file and add it to the value of Accruals for June 2013 in that other file".