calculation amounts for BANK & CASH across sheets based on matching partial item

[Omran Y]

Hi
the result should be in G2,G3 for BALANCES sheet.
in column (RECEIVED/PAID/NOT PAY) as in header for each sheet contains BANK or CASH should merge amounts are existed in last column with exclude AGGREGATE row from calculation across sheets first .
second should subtract the merged amounts from each other of them (RECEIVED -PAID) for BANK or CASH .
after that should sum the balance to B2,B3
example:
when merge RECEIVED for BANK for three sheets(SL,RS,VC)
RECEIVED BANK=40400+13400+480+120+200=54600
when merge PAID for BANK for four sheets(SL,RS,EP,VC)
PAID BANK=15000+2620+1100+1100+11500+1500+200+200=33200
after that should be 54600-33200=21380
final step when show the result in G2 for BANK should add to B2 will be like this=
21380+200000=221380 as show in G2
the same thing for CAH whether PAID or RECEIVED.
any suggestion to do that by formula or vba will be great.

BANK.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	DATE	CLIENT	ID	RECEIVED/PAID/NOT PAY	QTY	UNIT PRICE	TOTAL
2	1	01/01/2024	OMRAN	FSLOO90	RECEIVED CASH RE2000	200.00	100.00	20,000.00
3	2	01/01/2024	OMRAN	SDK-100	RECEIVED CASH RE2000	20.00	110.00	2,200.00
4	AGGREGATE							22,200.00
5	1	01/01/2024	OMAAR	TTR-1000	NOT PAY	22.00	120.00	2,640.00
6	AGGREGATE							2,640.00
7	1	02/01/2024	AMIR	TRE400-90	RECEIVED BANK SWIFT 234441222	120.00	110.00	13,200.00
8	2	02/01/2024	AMIR	TRE400-91	RECEIVED BANK SWIFT 234441222	12.00	130.00	1,560.00
9	3	02/01/2024	AMIR	TRE400-92	RECEIVED BANK SWIFT 234441222	122.00	120.00	14,640.00
10	4	02/01/2024	AMIR	TRE400-93	RECEIVED BANK SWIFT 234441222	100.00	110.00	11,000.00
11	AGGREGATE							40,400.00
12	1	02/01/2024	AMIR	TTR-1000	RECEIVED BANK CHEQUE 122	20.00	120.00	2,400.00
13	2	02/01/2024	AMIR	TTR-1001	RECEIVED BANK CHEQUE 122	100.00	110.00	11,000.00
14	AGGREGATE							13,400.00
SL

Cell Formulas
Range	Formula
H12:H13,H7:H10,H5,H2:H3	H2	=F2*G2
H4,H14	H4	=SUM(H2:H3)
H6	H6	=H5
H11	H11	=SUM(H7:H10)

BANK.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	DATE	CLIENT	ID	RECEIVED/PAID/NOT PAY	QTY	UNIT PRICE	TOTAL
2	1	01/01/2024	MUH	FSLOO90	PAID CASH AS2000	10.00	110.00	1,100.00
3	AGGREGATE							1,100.00
4	1	01/01/2024	NURI	FSLOO90	NOT PAY	22.00	110.00	2,420.00
5	AGGREGATE							2,420.00
6	1	02/01/2024	ANUR	FSLOO90	PAID BANK SWIFT 234441288	120.00	110.00	13,200.00
7	2	02/01/2024	ANUR	TRE400-91	PAID BANK SWIFT 234441288	12.00	150.00	1,800.00
8	AGGREGATE							15,000.00
9	1	02/01/2024	ANUR	FSLOO90	PAID BANK CHEQUE 12211	20.00	120.00	2,400.00
10	2	02/01/2024	ANUR	TTR-1001	PAID BANK CHEQUE 12211	10.00	22.00	220.00
11	AGGREGATE							2,620.00
12	1	02/01/2024	ANUR	FSLOO90	PAID CASH AS200011	20.00	120.00	2,400.00
13	AGGREGATE							2,400.00
14	1	02/01/2024	NURI	FSLOO90	NOT PAY	2.00	110.00	220.00
15	AGGREGATE							220.00
PR

Cell Formulas
Range	Formula
H2,H14,H12,H9:H10,H6:H7,H4	H2	=F2*G2
H3,H13	H3	=SUM(H2:H2)
H5,H15	H5	=H4
H8,H11	H8	=SUM(H6:H7)

BANK.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	DATE	CLIENT	ID	RECEIVED/PAID/NOT PAY	QTY	UNIT PRICE	TOTAL
2	1	02/01/2024	ANUR	FSLOO90	NOT PAY	1.00	120.00	120.00
3	2	02/01/2024	ANUR	TTR-1001	NOT PAY	1.00	22.00	22.00
4	AGGREGATE							142.00
5	1	02/01/2024	OMRAN	TTR-1001	PAID BANK SWIFT 25555	10.00	110.00	1,100.00
6	AGGREGATE							1,100.00
7	1	02/01/2024	ALI	TTR-1001	PAID BANK CHEQUE 122222	10.00	110.00	1,100.00
8	AGGREGATE							1,100.00
9	1	02/01/2024	ALI	FSLOO90	PAID CASH RE 1222212	10.00	110.00	1,100.00
10	AGGREGATE							1,100.00
RP

Cell Formulas
Range	Formula
H9,H7,H5,H2:H3	H2	=F2*G2
H4	H4	=SUM(H2:H3)
H6,H10,H8	H6	=SUM(H5)

BANK.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	DATE	CLIENT	EX	RECEIVED/PAID/NOT PAY	QTY	UNIT PRICE	TOTAL
2	1	02/01/2024	NN	LABOR	PAID BANK 123333	1.00	1,500.00	1,500.00
3	2	02/01/2024	NN	HIRE	PAID BANK 123333	2.00	5,000.00	10,000.00
4	AGGREGATE							11,500.00
5	1	02/01/2024	MMM	SERVICE	PAID BANK 123334	1.00	1,500.00	1,500.00
6	AGGREGATE							1,500.00
7	1	02/01/2024	SS	SALARY ESS	PAID CASH RE200011	1.00	2,000.00	2,000.00
8	2	02/01/2024	SS	SALARY ESD	PAID CASH RE200011	1.00	3,000.00	3,000.00
9	AGGREGATE							5,000.00
10	1	02/01/2024	TT	LABOR	PAID CASH RE200014	2.00	2,000.00	4,000.00
11	AGGREGATE							4,000.00
EP

Cell Formulas
Range	Formula
H10,H7:H8,H5,H2:H3	H2	=F2*G2
H4,H9	H4	=SUM(H2:H3)
H6	H6	=SUM(H5)
H11	H11	=SUM(H10:H10)

BANK.xlsm
	A	B	C	D	E
1	ITEM	DATE	CLIENT	RECEIVED/PAID/NOT PAY	TOTAL
2	1	02/01/2024	OMAAR	PAID BANK 4444	200.00
3	2	02/01/2024	NURI	PAID BANK 4445	200.00
4	3	02/01/2024	NURI	PAID CASH RE3001	110.00
5	4	02/01/2024	AMIR	PAID CASH RE3002	100.00
6	5	02/01/2024	SS	RECEIVED CASH RE400011	100.00
7	6	03/01/2024	SS	RECEIVED CASH RE400012	110.00
8	7	04/01/2024	SS	RECEIVED BANK SWIFT RE400013	120.00
9	8	05/01/2024	SS	RECEIVED BANK CHEQUE CR1000	200.00
VC

before

BANK.xlsm
	A	B	C	D	E	F	G
1	FIRST DURETION	BALANCES				ACCOUNTS	BALANCES
2	BANK	200,000.00				BANK
3	CASH	300,000.00				CASH
BALANCES

after

BANK.xlsm
	A	B	C	D	E	F	G
1	FIRST DURETION	BALANCES				ACCOUNTS	BALANCES
2	BANK	200,000.00				BANK	221,380.00
3	CASH	300,000.00				CASH	311,200.00
BALANCES

Cell Formulas
Range	Formula
G2:G3	G2	=B2+SL!L2-PR!K2