filter data based on month or whole data across sheets using dropdown

[Maklil]

Hello
I need filtering data by merge duplicates items for column C,D based on ID in column B across sheets with matching part of item using helper column (G) then should show the items based on column G in output sheet .
if cell B1 is empty then should merge for each ID for whole data and insert BALANCE column(E) to subtract column C from D , if I select specific month , then just merge based on month and insert BALANCE column(E) to subtract column C from D . the result should be in OUTPUT based on cell B1 and match part of the item in column G with others sheets in column B and insert TOTAL row to sum the whole columns and subtract from each other.
every time I will add new sheets before OUTPUT sheet. so should update data in OUTPUT sheet as I need it by clear data in OUPUT sheet .

TOTAL (1).xlsm
	A	B	C	D
1	DATE	BATCH	IMPORT	EXPORT
2	01/01/2023	STR ATM001 RS	2,000.00
3	02/01/2023	NO S001 STR1	2,200.00
4	03/01/2023	TRGG MMNR MSDF	2,400.00	1,200.00
5	04/01/2023	MMDT NTY AS002	2,600.00	1,300.00
6	05/01/2023	MSR	2,800.00	1,400.00
7	06/01/2023	ATM001 RS STR		1,500.00
8	07/01/2023	NO STR1 SFGG N		1,600.00
9	08/01/2023	STR		1,700.00
10	09/02/2023	NTY AS002 MMDT BN		1,800.00
11	10/02/2023	MSR NBH 1099U NMT66	3,800.00
12	11/02/2023	STR	4,000.00	2,000.00
13	12/02/2023	NO S001 CV678 STR1	4,200.00	2,100.00
14	13/02/2023	MMNR TRGG MSDF	4,400.00
15	14/02/2023	MMDT	4,400.00
16	15/02/2023	NBH 1099U MSR NMT66	4,400.00	2,200.00
CA

TOTAL (1).xlsm
	A	B	C	D
1	DATE	BATCH	IMPORT	EXPORT
2	11/01/2023	ATM001 RS MN99 STR	200.00
3	12/01/2023	NO S001 NBH100 STR1	200.00
4	13/01/2023	TRGG MNJ800 MMNR MSDF	3,000.00	100.00
5	14/01/2023	MMDT NTY AS002	200.00
6	15/01/2023	MSR	1,000.00	1,400.00
7	16/01/2023	ATM001 RS BYJ STR		600.00
8	17/01/2023	NO STR1 SFGG N	1,000.00	200.00
9	18/01/2023	STR	1,000.00	100.00
10	19/02/2023	NTY AS002 MMDT BN	2,000.00
11	20/02/2023	MSR NBH 1099U NMT66	3,800.00
12	21/02/2023	STR	4,000.00	2,000.00
13	22/02/2023	NO S001 CV678 STR1	4,200.00	2,100.00
14	23/02/2023	MMNR TRGG MSDF	4,400.00
15	24/02/2023	MMDT	4,400.00
16	25/02/2023	NBH 1099U MSR NMT66	4,400.00	2,200.00
CMN

when B1 is empty

TOTAL (1).xlsm
	A	B	C	D	E	F	G
1							ITEMS
2	BATCH	IMPORT	EXPORT	BALANCE			STR
3							STR1
4							MMNR
5							MMDT
6							MSR
7
8
9
10
OUTPUT

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=MONTHS

result

TOTAL (1).xlsm
	A	B	C	D
1
2	BATCH	IMPORT	EXPORT	BALANCE
3	STR	11,200.00	7,800.00	3,400.00
4	STR1	11,800.00	600.00	11,200.00
5	MMNR	14,200.00	1,300.00	12,900.00
6	MMDT	13,600.00	1,300.00	12,300.00
7	MSR	20,200.00	7,200.00	13,000.00
8	TOTAL	71,000.00	18,200.00	52,800.00
9
OUTPUT

Cell Formulas
Range	Formula
B8:C8	B8	=SUM(B3:B7)
D8	D8	=B8-C8

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=MONTHS

IF I select month in B1 = JAN

TOTAL (1).xlsm
	A	B	C	D	E	F	G
1		JAN					ITEMS
2	BATCH	IMPORT	EXPORT	BALANCE			STR
3	STR	3,200.00	3,900.00	-700.00			STR1
4	STR1	3,400.00	1,800.00	1,600.00			MMNR
5	MMNR	5,400.00	1,300.00	4,100.00			MMDT
6	MMDT	2,800.00	1,300.00	1,500.00			MSR
7	MSR	3,800.00	2,800.00	1,000.00
8	TOTAL	18,600.00	11,100.00	7,500.00
9
OUTPUT

Cell Formulas
Range	Formula
B8:C8	B8	=SUM(B3:B7)
D8	D8	=B8-C8

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=MONTHS

the data could be 2500 rows for each sheet , the sheets could be 20 sheets at least
thanks .

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.