PDA

View Full Version : Updating nested linked files



MrRhodes2004
01-10-2007, 11:50 AM
Hey Group,

I have a question with nested linked files. Let us say there are five files (A, B, C, D, E) that are linked in serial and internally cross-linked.

File A is the summary file that collects and processes the information contained in the other four files.

File B through File E are inter-related and interlinked. Their information is inter-dependant upon the other files.

To simplify the question, A is linked to B, B to C, C to D, and D to E.

When file A is opened, the values are automatically updated from B. If changes were made in file E, file A may not know or reflect those changes.

To make the question more difficult than it needs, if the information in file E causes a change in file B which then causes a change in file D . . . and so on. I control file A and others control (I do have master control though) files B through E.


Is there a way to ensure that when file A is opened and ?updates values? it is truly updating all the values from all the nested linked files?

They way I have been doing it manually is to open file A > edit links > select and open all source files. Then once all those files are opened, I do the same, opening all of the source files. This means having many files open at the same time. For the current project that I am working on, I had 15 files open at one time to make sure that the data is the summary was updated correctly.

Does Excel already make these changes? In my experience, it does not seem too. Is there a simplified code that allows this to happen automatically?

Thanks,

Michael

malik641
01-10-2007, 01:53 PM
Hey Michael,

From a quick test it looks like you are right, Excel needs to calculate "backwards" to get the information correct.

How big are these files? How often do you need them to be updated daily (each workbook, I mean)?

And which version of Excel are you running?

MrRhodes2004
01-10-2007, 02:21 PM
Joseph,

The files are about one MB each and some are much smaller than that. The problem in the current situation is not a length of time to open and run all the wbs but more of a user's knowledge. If I open and run the summary, I know that I have to work backward through all the other sheet to ensure that the summary is accurate. If I am not here and an uneducated user opens the file and tries to use the information, the data may be incorrect.

I'm not sure how to go about the update process. If all the files are opened from each referenced file and each one of those files, the information in the parent file will be updated. But what about each of those children files? Do they have to be saved? Is that a good thing to do (open a file of another, update the information, and save it)? Or if the data in the parent is updated and all the children files closed without saving, will the data in the parent remain updated?

I am running 2003SP2.

Michael

malik641
01-10-2007, 04:30 PM
The problem in the current situation is not a length of time to open and run all the wbs but more of a user's knowledge. ...If I am not here and an uneducated user opens the file and tries to use the information, the data may be incorrect. Maybe you should consider the design of the whole data structure. I mean, I don't know why you have it set up like that, and it might be for a good reason, so I might be wrong on this. But what about placing all your data in one centralized workbook and use the other workbooks to scrape off the data it needs to represent whatever data analysis you need to show. This way they all refer to one source, and would not depend on multiple workbooks to be updated correctly.


If all the files are opened from each referenced file and each one of those files, the information in the parent file will be updated. But what about each of those children files? They will be updated for the time being (as long as they are all in the same Excel Application instance). If you save one workbook it will be changed, but if you close the other workbooks without saving, it will return to the information that it was when you first opened the main workbook (if the main workbook is still opened after closing the child workbooks). If you don't save the main workbook after that, that's fine for THAT workbook, not the rest. But I wouldn't recommend this aproach to solving your problem.

Another problem with doing that is if you have other users on one or more of those files. Then you can only open them read-only, which is fine to have an updated version shown (the links will still work), but you cannot save the information with the original name (and I would not recommend doing with something like this).

...Come to think of it, for this situation maybe using shared workbooks would be the answer (I have never dealt with that before, but I think it could be very useful for your situation, if you are not already using it). That is if you can not consolidate all the workbook data into one workbook. Multiple users can edit the same workbooks at the same time and save the new data. I'm not sure how shared workbooks behave with crossing links like you have, but it might work out well. If you needed to, you could create an Excel Add-In (.xla) file with some code that could monitor which workbook you are opening and then open the other workbooks at the same time to update all the information, then save the workbooks that you didn't open and close them so now all the workbooks are updated correctly. Again, I'm not familiar with Shared workbooks so the coding may not work the way that I'm thinking of (or at least as easy as I'm making it in my head :) )