Hi guys,
I think an example is best here because it will get very confusing other wise.
- (Bottom level) Worksheet A - Original data
- (The next level up) Worksheet B - Has named range to original data and prints it using CSE (Ctrl+Shift+Enter) within the workbook.
- (the next level up from Worksheet B) Worksheet C - Has a reference Cell to worksheet B that is generated from Worksheet A's original data.
- (I know this seems like the smart idea would be to have worksheet C reference Worksheet A, but that's not the point of this.)
If worksheet B is opened without Worksheet A being opened, the CSE data comes up as #REF!, but any direct references will be updated.
If worksheet C is opened, it will populate with information from Worksheet B. but if Worksheet B has not been opened with worksheet A, then the data in worksheet B will be wrong and so will Worksheet C.
What this all boils down to is:
I need a VBA that will find any all sources of data within the workbooks (Data>Connections >Edit Links), open those work books up, then find any sources of data within THOSE workbooks and also open them up, and continue this until everything is open. Then updates workbooks from the bottom up until the actual workbook I wanted to open up has the correct data.
A better solution might be to build a list of directories and open files up with the level and the level above it to update the data, and then closes the bottom level.
Anyways,
if anyone has any ideas or a starting point' that would be great. Seems like a lot of work, but hopefully if it is done right should be usual in any worksheet.
Thanks
Jimmy
Bookmarks