Subtract cell value from last cell before TOTAL row

[Mussala]

Hello
I want to subtract cell value I2 from last cell before TOTAL row in column E and show the difference in adjacent cell in column F for last cell before TOTAL row in column E .
every time I add new data before TOTAL row , so here is examples

a.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL			AMOUNT
2	19/06/2023	INV001	4,220.00		4,220.00				24340
3	19/06/2023	INV002	5,000.00		9,220.00
4	19/06/2023	INV003	1,920.00		11,140.00
5	19/06/2023	INV004	270.00		11,410.00
6	19/06/2023	INV005	4,220.00		15,630.00
7	19/06/2023	INV006	3,700.00		19,330.00
8	19/06/2023	INV007	2,460.00		21,790.00
9	19/06/2023	INV008	1,000.00		22,790.00
10	19/06/2023	EXPENSES		44.00	22,746.00
11	19/06/2023	EXPENSES		30.00	22,716.00
12	19/06/2023	EXPENSES		65.00	22,651.00
13	19/06/2023	EXPENSES		11.00	22,640.00
14	TOTAL		22,790.00	150.00	22,640.00
s

result

a.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL			AMOUNT
2	19/06/2023	INV001	4,220.00		4,220.00				24340
3	19/06/2023	INV002	5,000.00		9,220.00
4	19/06/2023	INV003	1,920.00		11,140.00
5	19/06/2023	INV004	270.00		11,410.00
6	19/06/2023	INV005	4,220.00		15,630.00
7	19/06/2023	INV006	3,700.00		19,330.00
8	19/06/2023	INV007	2,460.00		21,790.00
9	19/06/2023	INV008	1,000.00		22,790.00
10	19/06/2023	EXPENSES		44.00	22,746.00
11	19/06/2023	EXPENSES		30.00	22,716.00
12	19/06/2023	EXPENSES		65.00	22,651.00
13	19/06/2023	EXPENSES		11.00	22,640.00	1,700
14	TOTAL		22,790.00	150.00	22,640.00
s

another example

a.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL			AMOUNT
2	19/06/2023	INV001	4,220.00		4,220.00				22500
3	19/06/2023	INV002	5,000.00		9,220.00
4	19/06/2023	INV003	1,920.00		11,140.00
5	19/06/2023	INV004	270.00		11,410.00
6	19/06/2023	INV005	4,220.00		15,630.00
7	19/06/2023	INV006	3,700.00		19,330.00
8	19/06/2023	INV007	2,460.00		21,790.00
9	19/06/2023	INV008	1,000.00		22,790.00
10	19/06/2023	EXPENSES		44.00	22,746.00
11	19/06/2023	EXPENSES		30.00	22,716.00
12	19/06/2023	EXPENSES		65.00	22,651.00
13	19/06/2023	EXPENSES		11.00	22,640.00
14	TOTAL		22,790.00	150.00	22,640.00
s

result

a.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL			AMOUNT
2	19/06/2023	INV001	4,220.00		4,220.00				22500
3	19/06/2023	INV002	5,000.00		9,220.00
4	19/06/2023	INV003	1,920.00		11,140.00
5	19/06/2023	INV004	270.00		11,410.00
6	19/06/2023	INV005	4,220.00		15,630.00
7	19/06/2023	INV006	3,700.00		19,330.00
8	19/06/2023	INV007	2,460.00		21,790.00
9	19/06/2023	INV008	1,000.00		22,790.00
10	19/06/2023	EXPENSES		44.00	22,746.00
11	19/06/2023	EXPENSES		30.00	22,716.00
12	19/06/2023	EXPENSES		65.00	22,651.00
13	19/06/2023	EXPENSES		11.00	22,640.00	-140
14	TOTAL		22,790.00	150.00	22,640.00
s

 

[Peter_SSs]

Is this what you mean?

23 06 20.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL			AMOUNT
2	19/06/2023	INV001	4220		4220				22500
3	19/06/2023	INV002	5000		9220
4	19/06/2023	INV003	1920		11140
5	19/06/2023	INV004	270		11410
6	19/06/2023	INV005	4220		15630
7	19/06/2023	INV006	3700		19330
8	19/06/2023	INV007	2460		21790
9	19/06/2023	INV008	1000		22790
10	19/06/2023	EXPENSES		44	22746
11	19/06/2023	EXPENSES		30	22716
12	19/06/2023	EXPENSES		65	22651
13	19/06/2023	EXPENSES		11	22640	-140
14	TOTAL		22790	150	22640
Row before total

Cell Formulas
Range	Formula
F2:F13	F2	=IF(A3="TOTAL",I$2-E2,"")

 

[Mussala]

Hi Peter ,
I want to do that automatically when write the amount in cell I2 , then show the value directly .

 

[Peter_SSs]

I want to do that automatically

Do you want vba then? You could try this Worksheet_Change event code with a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rTot As Range
  
  Set rTot = Columns("A").Find(What:="TOTAL")
  If Not rTot Is Nothing Then
    Application.EnableEvents = False
    Range("F2", Range("F" & Rows.Count).End(xlUp).Offset(1)).ClearContents
    Range("F" & rTot.Row - 1).Value = Range("I2").Value - Range("E" & rTot.Row - 1).Value
    Application.EnableEvents = True
  End If
End Sub

 

[Mussala]

perfect!
just I need highlight red when show minus value and if positive value then should delete color .
thank you .

 

[Peter_SSs]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rTot As Range
  
  Set rTot = Columns("A").Find(What:="TOTAL")
  If Not rTot Is Nothing Then
    Application.EnableEvents = False
    Range("F2", Range("F" & Rows.Count).End(xlUp).Offset(1)).Clear
    With Range("F" & rTot.Row - 1)
      .Value = Range("I2").Value - Range("E" & rTot.Row - 1).Value
      If .Value < 0 Then .Interior.Color = vbRed
    End With
    Application.EnableEvents = True
  End If
End Sub

 

[Mussala]

perfect !
thank you so much .

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.