Updating older Excel worksheet cells to MS365 Excel worksheet cells - Implicit Intersection?

[fred3]

I have an older worksheet that summarizes cells from a number of other worksheets.
All of the "other worksheets" are of the same structure - only with different data.

The original Summary builds a row for each of the other files and displays cell values or results of formulas with cell references.
The original Summary can SORT these rows in column value order - just typical Excel sorting.
Most of the cells are either filled with unchanging data - like the file's one path - or are filled from the source file cells.
One of the cells in each row helps fill in the cells in that row - e.g. the source file name with or without the file extension.
Common data that is used in many cells is in a column that has a Name assigned in the Name Manager.
For the cells referencing a source file cell or cells, there is a "template" row of that starts with '=' and has code that will allow the Summary cell to be properly filled for that row so the cell will reference the source file cells.
None of this requires opening the source files.

I'm not able to meet all these performance features using Excel in MS365.
The template cell contents don't seem to work to fill the related cells.
The Summary cells don't get filled when the worksheet is calculated.

Some have suggested using Excel VBA which would be OK but there are lots of cells being dealt with - so that seems more effort than should be pursued. It also detracts from working with the Summary directly.

I built a simple worksheet that seemed to show that implicit intersection was possible with MS365 but clearly I'm missing something.
- There are two columns of numbers and one of text. Each is assigned a Name. Then two columns of combinations of the first 3 columns.
In this case the row relationships are consistent in the combinations even though the Names apply to the entire column. That looks like implicit intersection.
But, in this case, the columns of combinations have all but the first row formulas in light gray. ???

 

[Debaser]

You have a dynamic array formula which is spilling from the first formula. For example, if you enter =A1:A10 in B1, it will automatically return data to B1:B10 but the formula will only actually be present in B1.

 

[fred3]

Hmm.. very interestiing. Thank you! Now I guess I know why the formulas are light gray except in the first cell. My question now is:
How did I get a dynamic array without asking for one? Talk about implicit!
I'm tackling this from a couple of viewpoints:
1) I'd like to simply "fix" the old worksheet while keeping the condition that the source WBs don't have to be open.
2) If that's not possible (I'm wondering what's good practice) perhaps writing all the needed data from each source WB into a file and then read the file or files into the Summary WB? How is that usually done?

The architecture for these files goes like this:
Each source file can be run separately if and when needed or wanted.
We're using the Solver in each source file when needed and this takes some time to run.
This independence of the source WBs is important as we would often add new ones, etc.

Then, the Summary file is modified to pick up any new source file names. It's run separately and gathers the current/existing data from the source files to display and sort. So it's like a master display panel with limited control over the source files.
The Summary file has macros/subs that will open all the source files and run the Solver in a batch mode for each. But the Solver runs are time-consuming enough that this would only be done perhaps overnight and isn't something that would be convenient to do just before reviewing and sorting the data.

Thanks again!

 

[Debaser]

You haven't provided information that would allow us to fix what's happening.
I would typically use Power query to consolidate all the workbooks in a folder.

 

[fred3]

Thank you so much! I didn't realize that there was more needed. Sorry for the inconvenience.
I have found the problem here - my poor documentation on this 10-year old model:
There is a subroutine that fills formulas to get data from other workbooks.
I've attached it.

 

[Debaser]

Maybe ask Brad since he wrote it? He's still on EE most days

That amends existing formula strings to update the paths used, but doesn't really tell us what the original formulas were, or what is happening with them now?

 

[fred3]

Well, everything seems to be working now in the bigger workbook context. I'm sure it was the need to run the Formula-Fill when new items were added.
Thanks Debaser for pointing out the dynamic array!