Sum from previous row, different column

[Maccers93]

hi all, I was wondering if I could add from a previous row in a different column.

from what I have pasted, for example the debit is add and the credit is subtract. in row 12 the credit(column G) would go into H, then the next row would either be debit or credit, whichever one it is would add or subtract from the previous balance in H and sum in the balance on the same row and loop through until the end.

any help would be appreciative, thanks in advance.

 

[Maccers93]

Apologies for the table format above, the table below will show better detail:

Workbook1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Nominal Account Details
2	Client:
3	Year End:
4
5
6	Account 001 Contracting Sales
7	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
8	463	318	43489		Details1	0	881.06	-881.06	SAI	711		N	N		0	N
9	464	318	43489		Details2	0	1233.48	-2114.54	SAI	711		N	N		0	N
10	465	318	43494		Details3	0	9647.58	-11762.1	SAI	711		N	N		0	N
11	466	318	43494		Details4	0	2378.85	-14141	SAI	711		N	N		0	N
12	467	318	43500		Details5	0	625.55	-14766.5	SAI	711		N	N		0	N
13	468	319	43532		Details6	0	6607.93	-21374.5	SAI	711		N	N		0	N
14	469	319	43550		Details7	0	651.98	-22026.4	SAI	711		N	N		0	N
15	470	319	43557		Details8	0	704.85	-22731.3	SAI	711		N	N		0	N
16	471	324	43591		Details9	0	2881.06	-25612.3	SAI	711		N	N		0	N
17	472	324	43600		Details10	0	3083.7	-28696	SAI	711		N	N		0	N
18	473	324	43607		Details11	0	792.95	-29489	SAI	711		N	N		0	N
19	474	324	43607		Details12	0	2114.54	-31603.5	SAI	711		N	N		0	N
20	475	324	43607		Details13	0	7048.46	-38652	SAI	711		N	N		0	N
21	476	324	43607		Details14	0	4405.29	-43057.3	SAI	711		N	N		0	N
22	477	324	43607		Details15	0	7048.46	-50105.7	SAI	711		N	N		0	N
23	478	324	43621		Details16	0	5726.87	-55832.6	SAI	711		N	N		0	N
24	479	324	43626		Details17	0	2114.54	-57947.2	SAI	711		N	N		0	N
25	480	324	43626		Details18	0	8810.57	-66757.7	SAI	711		N	N		0	N
26	481	324	43640		Details19	0	6167.4	-72925.1	SAI	711		N	N		0	N
27	482	324	43642		Details20	0	3524.23	-76449.4	SAI	711		N	N		0	N
28	483	324	43642		Details21	0	154.19	-76603.5	SAI	711		N	N		0	N
29	484	324	43644		Details22	0	4405.29	-81008.8	SAI	711		N	N		0	N
30	486	326	43650		Details23	0	14096.92	-95105.8	SAI	711		N	N		0	N
31	487	326	43651		Details24	0	16299.56	-111405	SAI	711		N	N		0	N
32	488	326	43662		Details25	0	440.53	-111846	SAI	711		N	N		0	N
33	489	326	43675		Details26	0	748.9	-112595	SAI	711		N	N		0	N
34	490	326	43675		Details27	0	1585.9	-114181	SAI	711		N	N		0	N
35	491	326	43684		Details28	0	4405.29	-118586	SAI	711		N	N		0	N
36	493	326	43684		Details29	0	6167.4	-124753	SAI	711		N	N		0	N
37	494	326	43686		Details30	0	5947.14	-130700	SAI	711		N	N		0	N
38	495	326	43690		Details31	0	2026.43	-132727	SAI	711		N	N		0	N
39	496	326	43690		Details32	0	6951.54	-139678	SAI	711		N	N		0	N
40	497	326	43696		Details33	0	4405.29	-144084	SAI	711		N	N		0	N
41	498	326	43696		Details34	0	792.95	-144877	SAI	711		N	N		0	N
42	499	326	43696		Details35	0	992.95	-145870	SAI	711		N	N		0	N
43	500	326	43696		Details36	0	1207.05	-147077	SAI	711		N	N		0	N
44	501	326	43703		Details37	0	2995.6	-150072	SAI	711		N	N		0	N
45	502	327	43712		Details38	0	2643.17	-152715	SAI	711		N	N		0	N
46	503	327	43718		Details39	0	7048.46	-159764	SAI	711		N	N		0	N
47	504	327	43743		Details40	0	4140.97	-163905	SAI	711		N	N		0	N
48	505	327	43742		Details41	0	8722.47	-172627	SAI	711		N	N		0	N
49	506	327	43755		Details42	0	881.06	-173508	SAI	711		N	N		0	N
50	507	327	43762		Details43	0	4030.84	-177539	SAI	711		N	N		0	N
51	508	327	43767		Details44	0	1145.37	-178685	SAI	711		N	N		0	N
52	509	330	43775		Details45	0	969.16	-179654	SAI	711		N	N		0	N
53	510	330	43775		Details46	0	149.78	-179804	SAI	711		N	N		0	N
54	511	330	#########		Details47	0	704.85	-180508	SAI	711		N	N		0	N
55	512	330	#########		Details48	0	440.53	-180949	SAI	711		N	N		0	N
56	513	330	43805		Details49	0	5101.32	-186050	SAI	711		N	N		0	N
57	514	330	43808		Details50	0	6105.73	-192156	SAI	711		N	N		0	N
58	515	330	43810		Details51	0	951.54	-193108	SAI	711		N	N		0	N
59	516	330	43810		Details52	0	1162.96	-194270	SAI	711		N	N		0	N
60	517	330	43810		Details53	0	7929.51	-202200	SAI	711		N	N		0	N
61	518	330	43810		Details54	0	704.85	-202905	SAI	711		N	N		0	N
62	519	330	#########		Details55	0	2643.17	-205548	SAI	711		N	N		0	N
63	520	330	43819		Details56	0	2466.96	-208015	SAI	711		N	N		0	N
64	521	330	43819		Details57	0	5726.87	-213742	SAI	711		N	N		0	N
65	780	336	43830		Details58	0	20264.32	-234006	JNL			N	N		0	N
66	785	336	43830		Details59	12026.43	0	-221980	JNL			N	N		0	N
67	1949	328	43743		Details60	20.48	0	-221959	PAI	812		N	N		0	N
68						12046.91	234006.2	-221959
69
70	Account 002 Barley Sales
71	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
72	522	343	43830		Details1	0	8005.87	-8005.87	SAI	711		N	N		0	N
73	778	337	43830		Details2	0	9620.01	-17625.9	JNL	999A		N	N		0	N
74						0	17625.88	-17625.9
75
76	Account 003 Premia
77	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
78	743	331	43830		Details- AE	0	10231.49	-10231.5	JNL			N	N		0	N
79						0	10231.49	-10231.5
80
81	Account 004 Grass/Silage/Straw
82	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
83	492	326	43684		Details	0	6000	-6000	SAI	711		N	N		0	N
84						0	6000	-6000
85
86	Account 111 Materials
87	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
88	2005	333	43830		Details	277.68	0	277.68	PAI	812		N	N		0	N
89						277.68	0	277.68
90
91	Account 112A Fertiliser Purchases
92	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
93	2000	333	43830		Details1	1425	0	1425	PAI	812		N	N		0	N
94	2002	333	43830		Details2	47.4	0	1472.4	PAI	812		N	N		0	N
95	2008	333	43830		Details3	3280	0	4752.4	PAI	812		N	N		0	N
96						4752.4	0	4752.4
97
98	Account 112C Seeds & Sprays
99	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
100	1899	322	43616		Details1	112	0	112	PAI	812		N	N		0	N
101	1899	322	43616		Details2	1552.04	0	1664.04	PAI	812		N	N		0	N
102	1943	325	43672		Details3	28	0	1692.04	PAI	812		N	N		0	N
103	1943	325	43672		Details4	223.58	0	1915.62	PAI	812		N	N		0	N
104	2009	333	43830		Details5	147.5	0	2063.12	PAI	812		N	N		0	N
105	2010	333	43830		Details6	1475	0	3538.12	PAI	812		N	N		0	N
106	2011	333	43830		Details7	742.5	0	4280.62	PAI	812		N	N		0	N
107	2012	333	43830		Details8	1230	0	5510.62	PAI	812		N	N		0	N
108	2013	333	43830		Details9	1626	0	7136.62	PAI	812		N	N		0	N
109	2017	334	43830		Details10	0	88.5	7048.12	PAC	812		N	N		0	N
110						7136.62	88.5	7048.12
111
112	Account 301 Wages and salaries
113	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
114
115	744	331	43830		Details - AE	21439	0	21439	JNL			N	N		0	N
116	772	336	43830		Details1	6580.96	0	28019.96	JNL			N	N		0	N
117	775	337	43830		Details2	19.64	0	28039.6	JNL	381		N	N		0	N
118						28039.6	0	28039.6
119
120	Account 302 Directors remuneration
121	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
122	745	331	43830		Details- AE	16500	0	16500	JNL			N	N		0	N
123	773	336	43830		Details1	9953.52	0	26453.52	JNL			N	N		0	N
124						26453.52	0	26453.52
125
126	Account 322 Rent payable
127	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
128	746	331	43830		Details- AE	1000	0	1000	JNL			N	N		0	N
129						1000	0	1000
Sheet2

 

[jtakw]

Hi,

I'm not sure if I understand your question, since your table Already has all the correct amounts in Column H,
but may be you manually entered them and wants a formula?

Book3.xlsx
	A	B	C	D	E	F	G	H
1	Nominal Account Details
2	Client:
3	Year End:
4
5
6	Account 001 Contracting Sales
7	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance
8	463	318	43489		Details1	0	881.06	-881.06
9	464	318	43489		Details2	0	1233.48	-2114.54
10	465	318	43494		Details3	0	9647.58	-11762.1
11	466	318	43494		Details4	0	2378.85	-14141
12	467	318	43500		Details5	0	625.55	-14766.5
13	468	319	43532		Details6	0	6607.93	-21374.5
14	469	319	43550		Details7	0	651.98	-22026.4
15	470	319	43557		Details8	0	704.85	-22731.3
16	471	324	43591		Details9	0	2881.06	-25612.3
17	472	324	43600		Details10	0	3083.7	-28696
18	473	324	43607		Details11	0	792.95	-29489
19	474	324	43607		Details12	0	2114.54	-31603.5
20	475	324	43607		Details13	0	7048.46	-38652
21	476	324	43607		Details14	0	4405.29	-43057.3
22	477	324	43607		Details15	0	7048.46	-50105.7
23	478	324	43621		Details16	0	5726.87	-55832.6
24	479	324	43626		Details17	0	2114.54	-57947.2
25	480	324	43626		Details18	0	8810.57	-66757.7
26	481	324	43640		Details19	0	6167.4	-72925.1
27	482	324	43642		Details20	0	3524.23	-76449.4
28	483	324	43642		Details21	0	154.19	-76603.5
29	484	324	43644		Details22	0	4405.29	-81008.8
30	486	326	43650		Details23	0	14096.92	-95105.8
31	487	326	43651		Details24	0	16299.56	-111405
32	488	326	43662		Details25	0	440.53	-111846
33	489	326	43675		Details26	0	748.9	-112595
34	490	326	43675		Details27	0	1585.9	-114181
35	491	326	43684		Details28	0	4405.29	-118586
36	493	326	43684		Details29	0	6167.4	-124753
37	494	326	43686		Details30	0	5947.14	-130700
38	495	326	43690		Details31	0	2026.43	-132727
39	496	326	43690		Details32	0	6951.54	-139678
40	497	326	43696		Details33	0	4405.29	-144084
41	498	326	43696		Details34	0	792.95	-144877
42	499	326	43696		Details35	0	992.95	-145870
43	500	326	43696		Details36	0	1207.05	-147077
44	501	326	43703		Details37	0	2995.6	-150072
45	502	327	43712		Details38	0	2643.17	-152715
46	503	327	43718		Details39	0	7048.46	-159764
47	504	327	43743		Details40	0	4140.97	-163905
48	505	327	43742		Details41	0	8722.47	-172627
49	506	327	43755		Details42	0	881.06	-173508
50	507	327	43762		Details43	0	4030.84	-177539
51	508	327	43767		Details44	0	1145.37	-178685
52	509	330	43775		Details45	0	969.16	-179654
53	510	330	43775		Details46	0	149.78	-179804
54	511	330	43780		Details47	0	704.85	-180508
55	512	330	43780		Details48	0	440.53	-180949
56	513	330	43805		Details49	0	5101.32	-186050
57	514	330	43808		Details50	0	6105.73	-192156
58	515	330	43810		Details51	0	951.54	-193108
59	516	330	43810		Details52	0	1162.96	-194270
60	517	330	43810		Details53	0	7929.51	-202200
61	518	330	43810		Details54	0	704.85	-202905
62	519	330	43811		Details55	0	2643.17	-205548
63	520	330	43819		Details56	0	2466.96	-208015
64	521	330	43819		Details57	0	5726.87	-213742
65	780	336	43830		Details58	0	20264.32	-234006
66	785	336	43830		Details59	12026.43	0	-221980
67	1949	328	43743		Details60	20.48	0	-221959
68						12046.91	234006.2	-221959
69
70	Account 002 Barley Sales
71	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance
72	522	343	43830		Details1	0	8005.87	-8005.87
73	778	337	43830		Details2	0	9620.01	-17625.9
74						0	17625.88	-17625.9
75
76	Account 003 Premia
77	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance
78	743	331	43830		Details- AE	0	10231.49	-10231.5
79						0	10231.49	-10231.5
Sheet826

Cell Formulas
Range	Formula
H78:H79,H72:H74,H8:H68	H8	=IF(A8<>"",N(H7)+F8-G8,H7)

 

[Maccers93]

Hi,

I'm not sure if I understand your question, since your table Already has all the correct amounts in Column H,
but may be you manually entered them and wants a formula?

Book3.xlsx
	A	B	C	D	E	F	G	H
1	Nominal Account Details
2	Client:
3	Year End:
4
5
6	Account 001 Contracting Sales
7	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance
8	463	318	43489		Details1	0	881.06	-881.06
9	464	318	43489		Details2	0	1233.48	-2114.54
10	465	318	43494		Details3	0	9647.58	-11762.1
11	466	318	43494		Details4	0	2378.85	-14141
12	467	318	43500		Details5	0	625.55	-14766.5
13	468	319	43532		Details6	0	6607.93	-21374.5
14	469	319	43550		Details7	0	651.98	-22026.4
15	470	319	43557		Details8	0	704.85	-22731.3
16	471	324	43591		Details9	0	2881.06	-25612.3
17	472	324	43600		Details10	0	3083.7	-28696
18	473	324	43607		Details11	0	792.95	-29489
19	474	324	43607		Details12	0	2114.54	-31603.5
20	475	324	43607		Details13	0	7048.46	-38652
21	476	324	43607		Details14	0	4405.29	-43057.3
22	477	324	43607		Details15	0	7048.46	-50105.7
23	478	324	43621		Details16	0	5726.87	-55832.6
24	479	324	43626		Details17	0	2114.54	-57947.2
25	480	324	43626		Details18	0	8810.57	-66757.7
26	481	324	43640		Details19	0	6167.4	-72925.1
27	482	324	43642		Details20	0	3524.23	-76449.4
28	483	324	43642		Details21	0	154.19	-76603.5
29	484	324	43644		Details22	0	4405.29	-81008.8
30	486	326	43650		Details23	0	14096.92	-95105.8
31	487	326	43651		Details24	0	16299.56	-111405
32	488	326	43662		Details25	0	440.53	-111846
33	489	326	43675		Details26	0	748.9	-112595
34	490	326	43675		Details27	0	1585.9	-114181
35	491	326	43684		Details28	0	4405.29	-118586
36	493	326	43684		Details29	0	6167.4	-124753
37	494	326	43686		Details30	0	5947.14	-130700
38	495	326	43690		Details31	0	2026.43	-132727
39	496	326	43690		Details32	0	6951.54	-139678
40	497	326	43696		Details33	0	4405.29	-144084
41	498	326	43696		Details34	0	792.95	-144877
42	499	326	43696		Details35	0	992.95	-145870
43	500	326	43696		Details36	0	1207.05	-147077
44	501	326	43703		Details37	0	2995.6	-150072
45	502	327	43712		Details38	0	2643.17	-152715
46	503	327	43718		Details39	0	7048.46	-159764
47	504	327	43743		Details40	0	4140.97	-163905
48	505	327	43742		Details41	0	8722.47	-172627
49	506	327	43755		Details42	0	881.06	-173508
50	507	327	43762		Details43	0	4030.84	-177539
51	508	327	43767		Details44	0	1145.37	-178685
52	509	330	43775		Details45	0	969.16	-179654
53	510	330	43775		Details46	0	149.78	-179804
54	511	330	43780		Details47	0	704.85	-180508
55	512	330	43780		Details48	0	440.53	-180949
56	513	330	43805		Details49	0	5101.32	-186050
57	514	330	43808		Details50	0	6105.73	-192156
58	515	330	43810		Details51	0	951.54	-193108
59	516	330	43810		Details52	0	1162.96	-194270
60	517	330	43810		Details53	0	7929.51	-202200
61	518	330	43810		Details54	0	704.85	-202905
62	519	330	43811		Details55	0	2643.17	-205548
63	520	330	43819		Details56	0	2466.96	-208015
64	521	330	43819		Details57	0	5726.87	-213742
65	780	336	43830		Details58	0	20264.32	-234006
66	785	336	43830		Details59	12026.43	0	-221980
67	1949	328	43743		Details60	20.48	0	-221959
68						12046.91	234006.2	-221959
69
70	Account 002 Barley Sales
71	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance
72	522	343	43830		Details1	0	8005.87	-8005.87
73	778	337	43830		Details2	0	9620.01	-17625.9
74						0	17625.88	-17625.9
75
76	Account 003 Premia
77	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance
78	743	331	43830		Details- AE	0	10231.49	-10231.5
79						0	10231.49	-10231.5
Sheet826

Cell Formulas
Range	Formula
H78:H79,H72:H74,H8:H68	H8	=IF(A8<>"",N(H7)+F8-G8,H7)

I was just curious as they were summed manually. I’m trying to write a vba code that would go through the regions to produce those results.

Apologies for lack of description, thank you for the reply!