fred3
Member
I have a system of data analysis WBs that was developed around 10 years ago.
There are *many* WBs that have the same structure. I've created a "template" WB just to create new ones. Each has different data in it.
There is a single WB that summarizes the data from a selected set of the others - rather a Summary or "Control Panel" for the system.
This was developed some 10 years ago with an older version of Excel.
Now I'm using MS365 desktop Excel and are having some trouble with the old WBs.
To be more specific, the structure of the Summary WB has rows where in the first few columns the path and filename and sheet name for each of the data WBs is listed. Then, in subsequent columns there are simple formulas that refer to cells in the respective path\filename\sheet\cell for that row.
This worked great because the entire set of rows could be SORTed while keeping those references lined up.
This seems to have stopped working. I've gotten explanations that suggest the source files have to be OPEN in order to grab data from them.
Also, I had assigned Names to the Path column where the paths are and to the Sheet column where the sheet names are and the references in the formulas could use those names and get data from the same row as above. I've gotten explanations that this is due to implicit intersection that has been done away with or changed.
Yet, nothing I've done seems to help get this working again.
The first question is: does this need to be fixed or have we just forgotten how to use the system? (I have also gotten suggestions that the reasons given above aren't really correct). That would be nice!
The second question is: what *new* approaches might be reasonable. There are over 70 cell references involved so some approaches seem unwieldy.
There are *many* WBs that have the same structure. I've created a "template" WB just to create new ones. Each has different data in it.
There is a single WB that summarizes the data from a selected set of the others - rather a Summary or "Control Panel" for the system.
This was developed some 10 years ago with an older version of Excel.
Now I'm using MS365 desktop Excel and are having some trouble with the old WBs.
To be more specific, the structure of the Summary WB has rows where in the first few columns the path and filename and sheet name for each of the data WBs is listed. Then, in subsequent columns there are simple formulas that refer to cells in the respective path\filename\sheet\cell for that row.
This worked great because the entire set of rows could be SORTed while keeping those references lined up.
This seems to have stopped working. I've gotten explanations that suggest the source files have to be OPEN in order to grab data from them.
Also, I had assigned Names to the Path column where the paths are and to the Sheet column where the sheet names are and the references in the formulas could use those names and get data from the same row as above. I've gotten explanations that this is due to implicit intersection that has been done away with or changed.
Yet, nothing I've done seems to help get this working again.
The first question is: does this need to be fixed or have we just forgotten how to use the system? (I have also gotten suggestions that the reasons given above aren't really correct). That would be nice!
The second question is: what *new* approaches might be reasonable. There are over 70 cell references involved so some approaches seem unwieldy.