Adding new row and automatically calculating the total amount at the bottom

[eazyyexcel]

Hello there,

Is there a way I could add a new row at the bottom of the table (just before the total sum), key in the new data and the final total will be automatically calculated? Currently I need to add a new row manually, then I copy and paste the previous row and I paste in the new row, delete all the data, and key in the amount. But I notice the total sum at the bottom is not being automatically calculated. I need to reformat the formula to add the calculations of the new row.

Would really appreciate anyone's help on this.

Thank you

 

[LazyBug]

Hello! Check if Automatic Calculations in the Formulas tab > Calculation group is selected:

 

[eazyyexcel]

Hello! Check if Automatic Calculations in the Formulas tab > Calculation group is selected:

Hi LazyBug

Thanks for the reply. I have checked and yes the calculation options is listed as automatic.

 

[eazyyexcel]

I found another solution but I need to keep doing this which is annoying.

I need to keep clicking "update formula to include cells". Is there a way around this??

 

[eazyyexcel]

Attached is the picture

 

[LazyBug]

Unfortunately I can't repro your problem. Could you post a sample of your table with XL2BB to understand the structure?

 

[eazyyexcel]

Unfortunately I can't repro your problem. Could you post a sample of your table with XL2BB to understand the structure?

Hi I have attached my file.

Now when I insert a new row at row 16, I copy the row 15 and paste it on the 16. Then I make the changes. I need the highlighted green total amount to capture the new numbers that I have added at row 16 but it isnt capturing the numbers. The new row doesnt capture the blue color "retained earnings" as well.

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		4,000.00
2
3
4	May-20	17/6/2020	002-1PC	400.00	400		4,000.00	100	100	200	Y
5	Jun-20	17/6/2020	002-2PC	400.00	400		RM 4,000.00	100	100	200	Y
6	Jul-20	24/8/2020	002-3PC	400.00	400		RM 4,000.00	100	100	200	Y
7	Aug-20	24/8/2020	002-4PC	400.00	400		RM 4,000.00	100	100	200	Y
8	Aug-20		002-5PC	100.00	100			25	25	50	Y
9	Sep-20	2/11/2020	002-6PC	400.00	400		RM 4,000.00	100	100	200	Y	17/11/2020
10	Oct-20	4/12/2020	002-7PC	400.00	400		RM 4,000.00	100	100	200	Y	17/12/2020
11	Nov-20	4/12/2020	002-8PC	100.00	100		RM 4,000.00	25	25	50	Y	17/12/2020
12	Nov-20	9/12/2020	002-9PC	200.00	200		RM 4,000.00	50	50	100	Y	17/12/2020
13	Dec-20	28/12/2020	002-10PC	400.00	400		RM 4,000.00	100	100	200	Y	17/1/2021
14	Jan-21	2/2/2021	002-11PC	400.00	400		RM 4,000.00	100	100	200	Y	13/2/2021
15	Feb-21	1/3/2021	002-12PC	400.00	400		RM 4,000.00	100	100	200	N
16				4000.00		0.00		1000.00	1000.00	2000
17									retained earnings + principal =	2000
18
test

Cell Formulas
Range	Formula
G4	G4	=SUM(B1-F4)
G10:G15,G5:G7	G5	=SUM(G4-F5)
G9	G9	=SUM(G7-F9)
D16,H16:J16	D16	=SUM(D4:D15)
F16	F16	=SUM(F4:F14)
J17	J17	=SUM(J4:J15,F4:F15)

Cells with Data Validation
Cell	Allow	Criteria
M4:M15	List	Cash Deposit,Cheque,Cash,Online Transfer

Thank you

 

[NoSparks]

If you use a true Excel table you can insert a new row and everything will move down as desired.
Here's a couple of links to member sites dealing with tables that can assist you

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

www.jkp-ads.com

 

[Fluff]

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: Adding new row and automatically calculating the total amount at the bottom
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.

 

[eazyyexcel]

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: Adding new row and automatically calculating the total amount at the bottom
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.

Sorry about that I wasnt aware. I will not do it again in the future