collection details invoices as accounts in debit, credit

[abdo meghari]

Hello,
before LIST sheet contains many sheets and increasable as I need it.
in list sheet will put PURCHASING ,SELLING RETURNS in credit column(D), SELLING ,PURCHASING RETURNS put in DEBIT column(C) as to RECEIVED should reverse to CREDIT column ,PAID to DEBIT column .
as to words in column B will brings after colon for each MOVEMENT word. when brings any word will be consecutive when move to next sheet until reach new movement.
the amounts will brings from TOTAL column , in first row will write date (end of date for previous year based on current year) BALANCE and if the amount is minus in A1 cell then will put in CREDIT column and also BALANCE column , if positive value in A1 cell then will put in DEBIT column and also BALANCE column after that will calculate for each next row in column DEBIT,CREDIT as formula shows .

LIST.xlsm
	A	B	C	D	E	F	G
1	BALANCE:-2,50000.00			NAME:BASHIR
2
3				MOVEMENT : PURCHASING
4	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
5	2024.01.02	114	1284	GC 1200R20 AZ0026 CHI	80	895	242560
6			1285	GC 1200R20 AZ0183 CHI	40	925
7			1385	GC 315/80R22.5 AT161 CHI	20	735
8			1287	GC 315/80R22.5 AZ126 CHI	20	735
9			1294	GC 315/80R22.5 AZ188 CHI	20	745
10			1241	BS 1200R20 G580 JAP	40	2035
11			1227	BS 215/70R15C R623 THI	8	425
12			1221	BS 205/70R15C R623 THI	12	405
13	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
14	2024.01.09	121	1287	GC 315/80R22.5 AZ126 CHI	14	735	10290
15	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
16	2024.01.10	125	1227	BS 215/70R15C R623 THI	2	425	850
17	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
18	2024.01.20	148	1287	GC 315/80R22.5 AZ126 CHI	26	735	19110
19	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
20	2024.01.20	149	1221	BS 205/70R15C R623 THI	50	405	391650
21			1227	BS 215/70R15C R623 THI	12	425
22			1241	BS 1200R20 G580 JAP	180	2035
23	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
24	2024.03.30	217	1285	GC 1200R20 AZ0183 CHI	10	1225	12250
25	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
26	2024.03.30	226	1227	BS 215/70R15C R623 THI	4	544	2176
27	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
28	2024.04.14	230	1285	GC 1200R20 AZ0183 CHI	40	1205	81950
29			1284	GC 1200R20 AZ0026 CHI	30	1125
30	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
31	2024.04.17	236	1284	GC 1200R20 AZ0026 CHI	20	1125	46600
32			1285	GC 1200R20 AZ0183 CHI	20	1205
PAGE 0

LIST.xlsm
	A	B	C	D	E	F	G
1	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
2	2024.01.021	566	1284	GC 1200R20 AZ0026 CHI	10	895	25550
3			1285	GC 1200R20 AZ0183 CHI	10	925
4			1385	GC 315/80R22.5 AT161 CHI	10	735
5	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
6	2024.04.17	567	1287	GC 315/80R22.5 AZ126 CHI	1	735	735
7	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
8	2024.04.18	568	1227	BS 215/70R15C R623 THI	10	425	4250
9	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
10	2024.01.021	569	1287	GC 315/80R22.5 AZ126 CHI	1	735	735
11				MOVEMENT : SELLING
12	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
13	2024.01.24	440	1306	BS 750R16 R230 JAP	10	775	7750
14	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
15	2024.01.24	441	1306	BS 750R16 R230 JAP	2	780	1560
16	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
17	2024.01.24	442	1306	BS 750R16 R230 JAP	1	780	2285
18			1305	BS 700R16 R230 JAP	1	770
19			1287	GC 315/80R22.5 AZ126 CHI	1	735
20	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
21	2024.01.25	443	1227	BS 215/70R15C R623 THI	2	425	850
22	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
23	2024.01.25	444	1287	GC 315/80R22.5 AZ126 CHI	1	735	735
PAGE 1

LIST.xlsm
	A	B	C	D	E	F	G
1	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
2	2024.01.26	445	1306	BS 750R16 R230 JAP	2	775	1550
3	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	2024.01.26	446	1306	BS 750R16 R230 JAP	1	780	780
5	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
6	2024.01.26	447	1306	BS 750R16 R230 JAP	1	780	1550
7			1305	BS 700R16 R230 JAP	1	770
8				MOVEMENT : SELLING RETURNS
9	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
10	2024.01.24	330	1306	BS 750R16 R230 JAP	2	775	1550
11	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
12	2024.01.24	331	1306	BS 750R16 R230 JAP	1	780	780
13	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
14	2024.01.24	332	1305	BS 700R16 R230 JAP	1	780	1560
15			1306	BS 750R16 R230 JAP	1	780
PAGE 2

LIST.xlsm
	A	B	C	D	E	F	G
1	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
2	2024.01.25	333	1227	BS 215/70R15C R623 THI	1	425	1160
3			1287	GC 315/80R22.5 AZ126 CHI	1	735
4	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
5	2024.01.25	334	1287	GC 315/80R22.5 AZ126 CHI	1	735	735
6				MOVEMENT : PURCHASING RETURNS
7	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
8	2024.01.22	220	1284	GC 1200R20 AZ0026 CHI	1	895	18925
9			1285	GC 1200R20 AZ0183 CHI	2	925
10			1385	GC 315/80R22.5 AT161 CHI	2	735
11			1287	GC 315/80R22.5 AZ126 CHI	2	735
12	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
13	2024.01.22	221	1241	BS 1200R20 G580 JAP	2	2035	4920
14			1227	BS 215/70R15C R623 THI	2	425
PAGE 3

LIST.xlsm
	A	B	C	D	E	F	G
1	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
2	2024.01.22	222	1284	GC 1200R20 AZ0026 CHI	1	895	1820
3			1285	GC 1200R20 AZ0183 CHI	1	925
4	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
5	2024.01.23	223	1241	BS 1200R20 G580 JAP	2	2035	4920
PAGE 4

LIST.xlsm
	A	B	C	D
1	DATE	DESCIBE	DEBIT	CREDIT
2	2024.01.10	RECEIVED VBGHH200	2,000.00
3	2024.01.10	PAID HJU8888		2,000.00
4	2024.01.11	RECEIVED VBGHH201	1,500.00
5	2024.01.12	PAID HJU8889		100.00
6	2024.01.13	PAID HJU8890		200
7	2024.01.14	PAID HJU8891		225
8	2024.01.15	RECEIVED VBGHH202	30000
9	2024.01.16	RECEIVED VBGHH203	40000
10
11
12
13
14
15
ABS

RESULT SHOULD BE IN LIST sheet

LIST.xlsm
	A	B	C	D	E
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE
2	31/12/2023	BALANCE	-	-250,000.00	-250,000.00
3	2024.01.02	PURCHASING	-	242,560.00	-492,560.00
4	2024.01.09	PURCHASING	-	10,290.00	-502,850.00
5	2024.01.10	PURCHASING	-	850.00	-503,700.00
6	2024.01.10	RECEIVED	-	2,000.00	-505,700.00
7	2024.01.10	PAID	2,000.00	-	-503,700.00
8	2024.01.11	RECEIVED	-	1,500.00	-505,200.00
9	2024.01.12	PAID	100.00	-	-505,100.00
10	2024.01.13	PAID	200.00	-	-504,900.00
11	2024.01.14	PAID	225.00	-	-504,675.00
12	2024.04.14	PURCHASING	-	81,950.00	-586,625.00
13	2024.01.15	RECEIVED	-	30,000.00	-616,625.00
14	2024.01.16	RECEIVED	-	40,000.00	-656,625.00
15	2024.04.17	PURCHASING	-	46,600.00	-703,225.00
16	2024.04.17	PURCHASING	-	735.00	-703,960.00
17	2024.04.18	PURCHASING	-	4,250.00	-708,210.00
18	2024.01.20	PURCHASING	-	19,110.00	-727,320.00
19	2024.01.20	PURCHASING	-	391,650.00	-1,118,970.00
20	2024.01.22	PURCHASING RETURNS	-	18,925.00	-1,137,895.00
21	2024.01.22	PURCHASING RETURNS	-	4,920.00	-1,142,815.00
22	2024.01.22	PURCHASING RETURNS	-	1,820.00	-1,144,635.00
23	2024.01.23	PURCHASING RETURNS	-	4,920.00	-1,149,555.00
24	2024.01.24	SELLING RETURNS	-	1,550.00	-1,151,105.00
25	2024.01.24	SELLING RETURNS	-	780.00	-1,151,885.00
26	2024.01.24	SELLING RETURNS	-	1,560.00	-1,153,445.00
27	2024.01.24	SELLING	7,750.00	-	-1,145,695.00
28	2024.01.24	SELLING	1,560.00	-	-1,144,135.00
29	2024.01.24	SELLING	2,285.00	-	-1,141,850.00
30	2024.01.25	SELLING RETURNS	-	1,160.00	-1,143,010.00
31	2024.01.25	SELLING RETURNS	-	735.00	-1,143,745.00
32	2024.01.25	SELLING	850.00	-	-1,142,895.00
33	2024.01.25	SELLING	735.00	-	-1,142,160.00
34	2024.01.26	SELLING	1,550.00	-	-1,140,610.00
35	2024.01.26	SELLING	1,550.00	-	-1,139,060.00
36	2024.03.30	PURCHASING	-	12,250.00	-1,151,310.00
37	2024.03.30	PURCHASING	-	2,176.00	-1,153,486.00
LIST

Cell Formulas
Range	Formula
E3:E37	E3	=E2+C3-D3