Dynamic SUM of multiple named tables (structured referencing)

[leolapa]

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":

=SUM(LOOKUP(2;1/(Stmt1[Balance]<>"");Stmt1[Balance]);LOOKUP(2;1/(Stmt2[Balance]<>"");Stmt2[Balance]);LOOKUP(2;1/(Stmt3[Balance]<>"");Stmt3[Balance]))

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.

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:

=SEQUENCE(COUNTA($3:$3))

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

="Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]"

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...

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).

[S]=SUBTOTAL(9;LOOKUP(2;1/(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(COUNTA($3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")<>"");INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(COUNTA($3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")))

[/S]

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:

=SUBTOTAL(9;INDEX(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]");MAX(ROW(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")))-ROW(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"[[#Headers];["&Stmt1[[#Headers];[Balance]]&"]]"))))

COUNTA:

=SUBTOTAL(9;INDEX(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]");SUBTOTAL(3;INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]"))))

INDIRECT/ADDRESS:

=SUBTOTAL(9;INDIRECT(ADDRESS(MAX(ROW(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")));COLUMN(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")))))

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...

INDEX + MAX/ROW:

INDEX +COUNTA:

INDEX + INDIRECT/ADDRESS:

Minisheet:

1D Spilled Sum Last Non-Empty Row from Several Tables (Dynamic) MINISHEET.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Total balance, ALL checking accounts →					$77.229,48		← STATIC FORMULA WON'T ALLOW DYNAMIC INCLUSION OF NEW STATEMENTS TABLES TO THE RIGHT OF THE 3 EXISTING ONES!!!
2
3	Bank of America					JPMorgan Chase					US Bank
4
5	Date	Transaction	Amount	Balance		Date	Transaction	Amount	Balance		Date	Transaction	Amount	Balance
6	Oct-20-2016	Beginning balance	$0,00	$0,00		Mar-12-2020	Beginning balance	$0,00	$0,00		May-2-2021	Beginning balance	$0,00	$0,00
7	Oct-21-2016	Deposit	$10.000,00	$10.000,00		Mar-12-2020	Deposit	$330.712,94	$330.712,94		May-3-2021	Deposit	$6,72	$6,72
8	Nov-1-2016	Wire transfer out	-$5.300,00	$4.700,00		Mar-16-2020	Investment instrument	-$30.000,00	$300.712,94
9	Nov-1-2016	Folio 1	-$1.550,64	$3.149,36		Mar-16-2020	Investment instrument	-$50.000,00	$250.712,94
10	Nov-1-2016	Folio 2	-$129,69	$3.019,67		Mar-17-2020	Investment instrument	-$50.000,00	$200.712,94
11	Nov-3-2016	Folio 3	-$1.774,75	$1.244,92		Mar-19-2020	Investment instrument	-$50.000,00	$150.712,94
12	Nov-4-2016	Debit card	-$174,96	$1.069,96		Mar-19-2020	Investment instrument	-$50.000,00	$100.712,94
13	Nov-8-2016	Debit card	-$60,17	$1.009,79		Apr-1-2020	Investment instrument	-$25.000,00	$75.712,94
14	Nov-9-2016	Debit card	-$20,80	$988,99		Apr-24-2020	Wire transfer out	-$25.000,00	$50.712,94
15	Nov-10-2016	Debit card	-$16,50	$972,49		Jun-23-2020	Deposit	$7.000,00	$57.712,94
16	Nov-10-2016	Deposit	$2.904,67	$3.877,16		Jun-27-2020	Deposit	$4.000,00	$61.712,94
17	Nov-11-2016	Debit card	-$36,30	$3.840,86		Jun-29-2020	Deposit	$4.000,00	$65.712,94
18	Nov-14-2016	Debit card	-$13,50	$3.827,36		SEQUENCE/ COUNT stmts	Columns text for INDIRECT	LOOKUP and INDIRECT			SUBTOTAL9	HYPOTHETICAL FULL "SPILL" FORMULA THAT WON'T WORK
19	Nov-16-2016	Folio 4	-$8.158,67	$4.331,31							(should spill, if source OK)
20	Nov-16-2016	Deposit	$5.000,00	$668,69		(spills OK)	(spills OK)	(DOES NOT SPILL!)
21	Nov-22-2016	Folio 5	-$275,47	$393,22		↓	↓	↓			↓	↓
22	Nov-22-2016	Deposit	$2.904,67	$3.297,89		1	Stmt1[Balance]	#N/A			#N/A	=SUBTOTAL(9;LOOKUP(2;1/(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")<>"");INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")))
23	Nov-22-2016	Debit card	-$31,59	$3.266,30		2	Stmt2[Balance]	APPROACH ONLY WORKS INDIVIDUALLY			NO "#" array, so NO "spill" formula
24	Nov-28-2016	Debit card	-$25,60	$3.240,70		3	Stmt3[Balance]
25	Nov-29-2016	Cash withdrawal	-$200,00	$3.040,70			Stmt1[[#Headers];[Balance]]
26	Nov-30-2016	TED Recebida	$8.600,00	$11.640,70			Stmt2[[#Headers];[Balance]]	↓			↓
27	Nov-30-2016	Debit card	-$14,90	$11.625,80			Stmt3[[#Headers];[Balance]]	$11.509,82			$0,00
28	Dec-1-2016	Debit card	-$20,50	$11.605,30				$65.712,94
29	Dec-1-2016	Folio 6	-$95,48	$11.509,82				$6,72
30
31								INDEX/MAX/ROW and INDIRECT			SUBTOTAL9	FULL "SPILL" FORMULA NO LONGER RETURNS AN ERROR, BUT STILL WON'T WORK
32											(should spill, if source OK)
33								(Spills, but with ERROR!)
34								↓			↓	↓
35									#VALUE!		#VALUE!		$0,00
36									#VALUE!		↓		$0,00
37									#VALUE!				$0,00
38								APPROACH ONLY WORKS INDIVIDUALLY			NO "#" array, so NO "spill" formula
39
40
41								↓			↓
42								$11.509,82			$77.229,48
43								$65.712,94
44								$6,72
45
46								INDEX/COUNTA (SUBTOTAL3) and INDIRECT			SUBTOTAL9	FULL "SPILL" FORMULA NO LONGER RETURNS AN ERROR, BUT STILL WON'T WORK
47											(should spill, if source OK)
48								(Spills, but with ERROR!)
49								↓			↓	↓
50									#VALUE!		$0,00		$0,00
51									#VALUE!		↓		$0,00
52									#VALUE!				$0,00
53								APPROACH ONLY WORKS INDIVIDUALLY (using COUNTA)			NO "#" array, so NO "spill" formula
54
55
56								↓			↓
57								$11.509,82			$77.229,48
58								$65.712,94
59								$6,72
60
61								INDIRECT and ADDRESS			SUBTOTAL9	FULL "SPILL" FORMULA NO LONGER RETURNS AN ERROR, BUT STILL WON'T WORK
62											(should spill, if source OK)
63								(Spills, but with ERROR!)
64								↓			↓	↓
65									#VALUE!		#VALUE!		$0,00
66									#VALUE!		↓		$0,00
67									#VALUE!				$0,00
68								APPROACH ONLY WORKS INDIVIDUALLY (using COUNTA)			NO "#" array, so NO "spill" formula
69
70
71								↓			↓
72								$11.509,82			$77.229,48
73								$65.712,94
74								$6,72
1D Sum Last Rows

Cell Formulas
Range	Formula
F1	F1	=SUM(LOOKUP(2,1/(Stmt1[Balance]<>""),Stmt1[Balance]),LOOKUP(2,1/(Stmt2[Balance]<>""),Stmt2[Balance]),LOOKUP(2,1/(Stmt3[Balance]<>""),Stmt3[Balance]))
G6,L6	G6	=Stmt1[@Transaction]
N6:N7,D6:D29,I6:I17	N6	=AGGREGATE(9,6,M$6:M6)
F22:F24	F22	=SEQUENCE(SUBTOTAL(3,$3:$3))
G22:G24	G22	="Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"["&Stmt1[[#Headers],[Balance]]&"]"
H22	H22	=LOOKUP(2,1/(INDIRECT($G$22#)<>""),INDIRECT($G$22#))
K22	K22	=SUBTOTAL(9,$H$22)
G25:G27	G25	="Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"[[#Headers];["&Stmt1[[#Headers],[Balance]]&"]]"
K27	K27	=SUBTOTAL(9,$I$27:$J$29)
H27:H29	H27	=LOOKUP(2,1/(INDIRECT($G22)<>""),INDIRECT($G22))
I35:I37	I35	=INDEX(INDIRECT($G$22#),MAX(ROW(INDIRECT($G$22#)))-ROW(INDIRECT($G$25#)))
K35,K65,K50	K35	=SUBTOTAL(9,I35#)
M35:M37	M35	=SUBTOTAL(9,INDEX(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"["&Stmt1[[#Headers],[Balance]]&"]"),MAX(ROW(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"["&Stmt1[[#Headers],[Balance]]&"]")))-ROW(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"[[#Headers];["&Stmt1[[#Headers],[Balance]]&"]]"))))
K42	K42	=SUBTOTAL(9,$H$42:$I$44)
H42:H44	H42	=INDEX(INDIRECT($G22),MAX(ROW(INDIRECT($G22)))-ROW(INDIRECT($G25)))
I50:I52	I50	=INDEX(INDIRECT($G$22#),SUBTOTAL(3,INDIRECT($G$22#)))
M50:M52	M50	=SUBTOTAL(9,INDEX(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"["&Stmt1[[#Headers],[Balance]]&"]"),SUBTOTAL(3,INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"["&Stmt1[[#Headers],[Balance]]&"]"))))
K57	K57	=SUBTOTAL(9,$H$57:$I$59)
H57:H59	H57	=INDEX(INDIRECT($G22),COUNTA(INDIRECT($G22)))
I65:I67	I65	=INDIRECT(ADDRESS(MAX(ROW(INDIRECT($G$22#))),COLUMN(INDIRECT($G$22#))))
M65:M67	M65	=SUBTOTAL(9,INDIRECT(ADDRESS(MAX(ROW(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"["&Stmt1[[#Headers],[Balance]]&"]"))),COLUMN(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3,$3:$3))))&"["&Stmt1[[#Headers],[Balance]]&"]")))))
K72	K72	=SUBTOTAL(9,$H$72:$I$74)
H72:H74	H72	=INDIRECT(ADDRESS(MAX(ROW(INDIRECT($G22))),COLUMN(INDIRECT($G22))))
Dynamic array formulas.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dynamic SUM of multiple named tables (structured referencing)
and Dynamic SUM of multiple named tables (structured referencing)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[leolapa]

I appreciate your heads up on this and apologize for the inconvenience. I'll make sure to post the links next time...