Hi,
Let’s say you have 3 workbooks – A, B and C. C is the consolidated workbook which sums up figures from workbook A and B.
First of all, when working with multiple workbooks, links in C will get updated (if any change is made in A and/or B), provided the link in C to A and/or B is upto 255 characters. Characters beyond 255 (this limit has been relaxed in Excel 2007) are truncated and therefore links may not work as expected unless A and B are also opened. Therefore your problem is well understood.
Also, to update links in C, one would have to press F2 in the respective cells in C. This obviously is not a working solution.
Possible Solution/Workaround
This is what you could do:
1. Open all the files which are cross linked (A, B and C);
2. In Excel 2003, go to File > Save workspace and give it a suitable name (say consolidation);
Now what has happened is that you have created a “workspace” file which stores the layout of all the open workbooks. Whenever you want to open C, then instead of double clicking on C, double click on the Consolidation file. You would notice that all three files would open up and therefore any changes made in A and B would be reflected in C. Please also note that the workspace file named Consolidation only stores the layout of the individual files and nothing else. Therefore, if you want to mail the individual workbooks to somebody, I suggest that you also mail the workspace file with instructions that he must double click on the workspace file.
Also, please note that some Excel functions such as SUMIF(), COUNTIF() do not work when workbooks are closed – you will get an error. When you open the individual worksheets i.e. base data sheets, the result would appear. Therefore the File > Workspace method could come to our rescue.
Hope this helps. Please let me know how this works for you.