Create report by merging amounts for specific sheets together for each name

[KalilMe]

Hi
I want merging duplicates names for sheets SALES and FIRST .
in SALES sheet should merge the same name based on column C for amount in column J for TOTAL row in this case CR-10000= 300+26000 =26300
and FIRST sheet should merge the name based on column B
then CR-10000=100
so the result should show in column C=26300+100=26400 in LIST sheet.
and PAID sheet should merge the name based on column B
then CR-10000=100+100=200
the result should show in column D=200 in LIST sheet
as to column E for LIST sheet should subtract column C from column D .
as to DATE in column A should populate based on DATE(TODAY) for LIST sheet.
and so on the others names across sheets with considering the data will increase across sheets

CR.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	DATE	NAME	INVOICE	GOODS	TYPE	PR	QTY	UNIT	TOTAL
2	1	26/05/2023	CR-10000	FRVG-10000	ATR	AM1	GR	10	10.00	100.00
3	2	26/05/2023	CR-10000	FRVG-10000	ATR	AM2	PO	10	20.00	200.00
4	TOTAL	26/05/2023	CR-10000	FRVG-10000						300.00
5	1	26/05/2023	CR-10001	FRVG-10001	ATR	AM1	GR	10	22.00	220.00
6	2	26/05/2023	CR-10001	FRVG-10001	ATR	AM2	PO	10	33.00	330.00
7	TOTAL	26/05/2023	CR-10001	FRVG-10001						550.00
8	1	26/05/2023	CR-10002	FRVG-10002	ATR	AM1	GR	10	10.00	100.00
9	2	26/05/2023	CR-10002	FRVG-10002	ATR	AM1	PO	20	10.00	200.00
10	3	26/05/2023	CR-10002	FRVG-10002	ATR	AM4	QW	10	200.00	2,000.00
11	TOTAL	26/05/2023	CR-10002	FRVG-10002						2,300.00
12	1	26/05/2023	CR-10000	FRVG-10003	ATR	AM1	GR	20	100.00	2,000.00
13	2	26/05/2023	CR-10000	FRVG-10003	ATR	AM1	PO	20	200.00	4,000.00
14	3	26/05/2023	CR-10000	FRVG-10003	ATR	AM4	QW	200	100.00	20,000.00
15	TOTAL	26/05/2023	CR-10000	FRVG-10003						26,000.00
SALES

Cells with Data Validation
Cell	Allow	Criteria
E2:E4	List	=#REF!
F2:F4	List	=TYPE
G2:G4	List	=PR
E6:E7	List	=#REF!
F6:F7	List	=TYPE
G6:G7	List	=PR
E10:E11	List	=#REF!
F10:F11	List	=TYPE
G10:G11	List	=PR
E14:E15	List	=#REF!
F14:F15	List	=TYPE
G14:G15	List	=PR

CR.xlsm
	A	B	C	D	E
1	DATE	NAME	VOUCHER NO	CASE	AMOUNT
2	26/05/2023	CR-10000	VCH001	CASH	100.00
3	26/05/2023	CR-10000	VCH002	CASH	100.00
4	26/05/2023	CR-10002	VCH003	CASH	120.00
5
6
7
8
9
10
11
PAID

CR.xlsm
	A	B	C
1	DATE	NAME	OPENING
2	26/05/2023	CR-10000	100.00
3	26/05/2023	CR-10002	-300.00
4
5
6
7
8
9
10
11
FIRST

before

CR.xlsm
	A	B	C	D	E
1	DATE	NAME	DEBIT	CREDIT	BALANCE
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
LIST

after

CR.xlsm
	A	B	C	D	E
1	DATE	NAME	DEBIT	CREDIT	BALANCE
2	26/05/2023	CR-10000	26,400.00	200.00	26,200.00
3	26/05/2023	CR-10001	550.00		550.00
4	26/05/2023	CR-10002	2,300.00	-300.00	2,000.00
5
6
7
8
9
10
11
12
13
14
15
16
LIST

thanks