split tables into multiple tables based on two criteria and calculate values

[tubrak]

hi all
this is the firs post I hope find solution to my request despite it's complicated so what I want split the data for any customer to table and calculate the values
based on two criteria if the cells B2= "" then split all data of customers to multiple tables from sheet DATA to sheet RESULT and if cell b2 = specific name then should just bring data for this customer and I put some formulas to show how calculate but I would show as in value not formulas in cells
sheet DATA

Microsoft Excel Worksheet 1.xlsx
	A	B	C	D	E	F
1	ITEM	DATE	NAME	INVOICE NO	DEBIT	CREDIT
2	1	01/01/2021	Tubriko	AS-12	2,000.00	-
3	2	01/02/2021	Tubriko	AS-12		100.00
4	3	03/05/2021	Tubriko	AS-12		1,500.00
5	4	05/05/2021	Tubriko	AS-13	1,000.00
6	5	05/05/2021	Triboko	AS-14	2,000.00
7	6	02/02/2021	Triboko	AS-15	3,000.00
8	7	02/04/2021	Triboko	AS-14		1,500.00
9	8	05/04/2021	Triboko	AS-15		2,000.00
10	9	04/04/2021	tarkino	AS-16	2,500.00
11	10	04/04/2021	tarkino	AS-17	150.00
12	11	04/04/2021	tarkino	AS-16		200.00
DATA

Result based on condition1 in sheet RESULT

Microsoft Excel Worksheet 1.xlsx
	A	B	C	D	E	F	G
2	name
3
4		NAME	DEBIT	CREDIT	BALANCE
5		Tubriko	3,000.00	1,600.00	1,400.00
6
7	ITEM	DATE	NAME	INVOICE NO	DEBIT	CREDIT	BALANCE
8	1	01/01/2021	Tubriko	AS-12	2,000.00		2,000.00
9	2	01/02/2021	Tubriko	AS-12		100.00	1,900.00
10	3	03/05/2021	Tubriko	AS-12		1,500.00	400.00
11	4	05/05/2021	Tubriko	AS-13	1,000.00		1,400.00
12
13		NAME	DEBIT	CREDIT	BALANCE
14		Triboko	5,000.00	3,500.00	1,500.00
15
16	ITEM	DATE	NAME	INVOICE NO	DEBIT	CREDIT	BALANCE
17	1	05/05/2021	Triboko	AS-14	2,000.00		2,000.00
18	2	02/02/2021	Triboko	AS-15	3,000.00		5,000.00
19	3	02/04/2021	Triboko	AS-14		1,500.00	3,500.00
20	4	05/04/2021	Triboko	AS-15		2,000.00	1,500.00
21
22		NAME	DEBIT	CREDIT	BALANCE
23		tarkino	2,650.00	200.00	2,450.00
24
25	ITEM	DATE	NAME	INVOICE NO	DEBIT	CREDIT	BALANCE
26	9	04/04/2021	tarkino	AS-16	2,500.00		2,500.00
27	10	04/04/2021	tarkino	AS-17	150.00		2,650.00
28	11	04/04/2021	tarkino	AS-16		200.00	2,450.00
RESULT

Cell Formulas
Range	Formula
C23:D23,C14:D14,C5:D5	C5	=SUM(E8:E11)
E5,G26,E23,G17,E14,G8	E5	=C5-D5
G27:G28,G18:G20,G9:G11	G9	=G8+E9-F9

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Turbiko;Triboko;tarkino

expected result based on condition 2 in SHEET RESULT

Microsoft Excel Worksheet 1.xlsx
	A	B	C	D	E	F	G
1	name	Turbiko
2
3		NAME	DEBIT	CREDIT	BALANCE
4		Tubriko	3,000.00	1,600.00	1,400.00
5
6	ITEM	DATE	NAME	INVOICE NO	DEBIT	CREDIT	BALANCE
7	1	01/01/2021	Tubriko	AS-12	2,000.00		2,000.00
8	2	01/02/2021	Tubriko	AS-12		100.00	1,900.00
9	3	03/05/2021	Tubriko	AS-12		1,500.00	400.00
10	4	05/05/2021	Tubriko	AS-13	1,000.00		1,400.00
RESULT1

Cell Formulas
Range	Formula
C4:D4	C4	=SUM(E7:E10)
E4,G7	E4	=C4-D4
G8:G10	G8	=G7+E8-F8

Cells with Data Validation
Cell	Allow	Criteria
B1:B2	List	Turbiko;Triboko;tarkino
C1:C2	List	JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC

thanks in advance

 

[tubrak]

any helps ?

 

[tubrak]

sorry maybe this makes confusion in image 3 should write the name in B2 not B1 as I explained

 

[tubrak]

isn't clear my thread ?