Recalculate cells after autofilter

[honkin]

I have a sheet which records profit and loss for horse racing and I am wondering how it would be possible to have the Total Win Equity (CR) be dynamic.

So it is an ongoing tally of the profit plus the starting bank. These are the formulas =CR18+AD19 AD is the Profit or Loss. So it adds the profit of the current row + the Total Win Equity of the row above to show the current equity. The sheet has quite a lot of filters and when autofiltering is applied, naturally the Total Win Equity becomes incorrect, as not every row is there. I actually want that column to have the ability to tally what is visible, so CR of the row above + AD of the current row.

Is it at all possible that this can happen? I have been trying to think of a solution, but have come up short. Even SUBTOTAL doesn't see to cover it as whatever rows are visible will always need to reference the previous visible row and not just the previous numerical row. Does that make sense at all?

As you see here

2018 Latest Results June 2021.xlsb
	CR
17	Total Win Equity
18	10303.8
19	10203.8
20	10103.8
21	10003.8
22	9903.8
23	9803.8
31	9003.8
32	8903.8
sheet1

Cell Formulas
Range	Formula
CR18	CR18	=AD18+10000
CR31:CR32,CR19:CR23	CR19	=CR18+AD19

So the rows down to 23 are consecutive but I have hidden rows 24-30, just as may happen with autofiltering. Notice how the figure in row 31 has dropped substantially from that in row 23? The goal would be to have thing recalculate so that the accurate figures show in each cell

Hopefully it is achievable.

Thanks in advance

 

[Toadstool]

Hi Honkin,

Does this help?

Honkin7.xlsx
	AD	AE	AF																																																															CQ	CR
17	AD18 on		Filter																																																																10000
18	100		1																																																																10100
19	200		1																																																																10300
20	300		1																																																																10600
21	400		1																																																																11000
22	500		1																																																																11500
23	600		1																																																																12100
24	700																																																																		12800
25	800																																																																		13600
26	900																																																																		14500
27	1000																																																																		15500
28	1100																																																																		16600
29	1200																																																																		17800
30	1300																																																																		19100
31	1400		1																																																																20500
32	1500		1																																																																22000
Sheet1

Cell Formulas
Range	Formula
AD19:AD32	AD19	=AD18+100
CR18:CR32	CR18	=AGGREGATE(9,7,AD18)+INDEX(CR:CR,AGGREGATE(14,7,ROW($CR$17:$CR17),1))

 

[Fluff]

Another option

=AGGREGATE(9,7,AD$18:AD18)+10000

 

[Peter_SSs]

Also with SUBTOTAL

=10000+SUBTOTAL(109,AD$18:AD18)

 

[honkin]

Another option

=AGGREGATE(9,7,AD$18:AD18)+10000

Looks like 2 ways to skin this cat, Fluff. This appears to work fine, as does Peter_SSs' solution

Thanks so much. Not really sure how I mark the solution, with both giving identical results

 

[honkin]

Also with SUBTOTAL

=10000+SUBTOTAL(109,AD$18:AD18)

Cheers Peter_SSs
As I indicated to Fluff, there is obviously 2 ways to handle this, with both solutions giving identical results, even when the sheet is autofiltered.

I'm unsure how to mark both solutions as correct, so would love some advice how I achieve that

cheers

 

[honkin]

cheers

Hi Honkin,

Does this help?

Honkin7.xlsx
	AD	AE	AF																																																															CQ	CR
17	AD18 on		Filter																																																																10000
18	100		1																																																																10100
19	200		1																																																																10300
20	300		1																																																																10600
21	400		1																																																																11000
22	500		1																																																																11500
23	600		1																																																																12100
24	700																																																																		12800
25	800																																																																		13600
26	900																																																																		14500
27	1000																																																																		15500
28	1100																																																																		16600
29	1200																																																																		17800
30	1300																																																																		19100
31	1400		1																																																																20500
32	1500		1																																																																22000
Sheet1

Cell Formulas
Range	Formula
AD19:AD32	AD19	=AD18+100
CR18:CR32	CR18	=AGGREGATE(9,7,AD18)+INDEX(CR:CR,AGGREGATE(14,7,ROW($CR$17:$CR17),1))

Cheers Toadstool, but not really keen to look at a helper column, so will look to other solution. Thanks so much for your reply

 

[Peter_SSs]

I'm unsure how to mark both solutions as correct, so would love some advice how I achieve that

You can only mark one. Some choices would be ..

• The one you chose to use, perhaps because you understand it better or feel you could modify it easier if required later
• The one that was posted first
• Toss a coin
• ....

Neither Fluff nor I will be offended if you mark the other's post.

Main thing is that you got something that worked for you.

 

[honkin]

You can only mark one. Some choices would be ..

• The one you chose to use, perhaps because you understand it better or feel you could modify it easier if required later
• The one that was posted first
• Toss a coin
• ....

Neither Fluff nor I will be offended if you mark the other's post.

Main thing is that you got something that worked for you.

Thanks for your reply Peter. I have not currently chosen which one to choose. I had put both solutions in columns side by side to test how they performed when autofiltering, which was how I saw they were identical.

As for the one posted first, it is pretty tight, with both being posted within 20 minutes of each other. I just did it the scientific way and tossed a coin. My thanks to both of you, as it is invaluable

cheers

 

[Peter_SSs]

I just did it the scientific way and tossed a coin.

?