Source File not updating Destination Files in Excel

Question

I have one source file that holds all my input costs. I then have 30 to 40 destination files (costing sheets) that use links to data in this source file for their various formulae.

I was sure when I started this system that any changes I made to the source file, including the insertion of new rows and columns was updated automatically by the destination files, such that the formula always pulled the correct input costs.

Now all of a sudden if my destination files are closed and I change the structure of the source file by adding rows - the destination files go haywire? They pick up changes to their linked cells, but don't pick up changes to the source sheet that have shifted their relative positions in the sheet.

Do I really need to open all 40 destination files at the same time I alter the source file structure?

Further info: all the destination files are protected, and I am working on DropBox.

Answer 1

Unfortunately, I think the destination files do have to be open when you chance the source file.

I have run into this exact problem with having a central source file and a large number of dependent files. I changed some of the columns in the source file with the destination files closed, and my destination files lost sync with it.

One thing you might try if you are using Excel 2007 or up and if you are using lookup is to put the source data in a table and refer to the names of the columns/rows in the destination files. Never tried this, but it just might work. It also simplifies a whole lot of formulas.

Therefore, instead of feeding a range like A:100,A:1000, you just give it Table1("Date") or something similar.

EDIT: Seeing as how you have tagged this with Excel 2003, this will not be a workable solution for you to my knowledge. When I save my tables in Excel 2003 format, I get warnings about backwards compatibility with table references.

Actually, an ideal solution would be to migrate to an Access database, as this is far better at relating data in different tables