Dynamic SUM of multiple named tables (structured referencing)

**leolapa** · 06-03-2021, 12:23 AM

The sample schedule below presents 3 sets of bank statements from different accounts/institutions, each one under separate named tables (Stmt1, Stmt2, Stmt3). By using structured referencing it helps the effort of keeping track of the latest balance for each statement as they keep growing over time.

At the very top, cell F1 dynamically sums the latest balance (last populated row) of each statement in order to inform the user the grand total of cash "on hand":

Formula:

Please Login or Register to view this content.

So far, so good........ until there's always a BUT.........

In case I keep opening up new checking/savings/trading accounts and therefore new statements (Stmt4, Stmt5 and so on) start popping up to the right of the existing ones (column O onwards), HOW CAN I MAKE THE FORMULA ON F1 TO AUTOMATICALLY/DYNAMICALLY ADD THOSE NEW NAMED TABLES TO ITS SUM?

The obvious answer on such simple schedule is to just manually copy/paste the LOOKUP formula and change to the newly added table name. However my real life workbook is way more complex than that, and such task would not only be a chore but I also risk forgetting to incorporate those new statements to the tally formula. Thus I need a formula that recognizes as new statements are added so I don't have to worry about doing that in the future, and without recurring to VBA coding or helper columns.

1D Spilled Sum Last Non-Empty Rows.png

So I started my attempt to get to a solution by naming each table the same + a sequential number 1, 2, 3, etc. as this allows a recognition mechanism as new tables are added. Then I created a SEQUENCE formula based on the COUNTA of statement titles on row 3:

Formula:

Please Login or Register to view this content.

And this enabled me to concatenate the structured referencing for all "Balance" columns on each statement:

Formula:

Please Login or Register to view this content.

And as shown on the screenshot that follows I seemed to be on the right track to get to an INDIRECT "spilled" type solution that should do the trick just fine...

1D Spilled Sum Last Non-Empty Rows SEQUENCE, INDIRECT Text Spill.png

But that's when things started to go south......

I tried 4 different approaches to dynamically pick up the latest balance/last populated row of each statement table, but none of them worked in a way that would provide a dynamic solution that will ensure all future statements be integrated in the sum without further finagling.

My first go was by using the same LOOKUP(2;1/.... approach that returns the last non-empty row of a column. But this one is a no starter as it does not return a cell reference, so SUBTOTAL won't even take it (SUM/AGGREGATE do take nested functions that return values, but won't deal with spilled ranges so they are not feasible options).

Formula:

Please Login or Register to view this content.

1D Spilled Sum Last Non-Empty Rows LOOKUP.png

Then I attempted 3 different combinations of INDEX, and they all managed to get to a "spilled" solution but returning ZERO amount, and by decomposing each formula I realized that despite achieving spilled solutions they all return errors.

MAX/ROW:

Formula:

Please Login or Register to view this content.

COUNTA:

Formula:

Please Login or Register to view this content.

INDIRECT/ADDRESS:

Formula:

Please Login or Register to view this content.

It can be seen on each screenshot for every approach that they all do work individually (ORANGE sections), but this way I would get to the same static formula that won't automatically incorporate further statements.

What am I doing wrong here? Is there any other way to go about that I can't seem to grasp? Any help on this is greatly appreciated!

Below I added screenshots for each of the above mentioned INDEX combinations...

1D Spilled Sum Last Non-Empty Rows INDEX.png

**AliGW** · 06-03-2021, 01:42 AM

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)