sum or subtract for the same cell based on match column between two sheets

[Omran Y]

Hello
I want match names in column C for EMPLOYEE sheets with column B for TT sheet if the column C in TT sheet contains Advance payment then should subtract amount for adjacent cell for the name in EMPLOYEE sheet from amount for adjacent cell for the word Advance payment
for instance OMAR ALI OMAR =-20000-2000=-22000 as show in third picture as highlighted cell in D2 and change adjacent cell for NET AMOUNT by sum amount for adjacent NAME and adjacent cell for SALARY to become -22000+3000= -19000.
if the column C in TT sheet contains Pay payment then should sum amount for adjacent cell for the name in EMPLOYEE sheet from amount for adjacent cell for the word Pay payment
for instance AHMED OMAR ALI=-2000+3000=1000 as show in last sheet as highlighted cell in D8 and change adjacent cell for NET AMOUNT by sum amount for adjacent NAME and adjacent cell for SALARY to become 1000+3000= 4000.
and when repeat the same name in TT sheet then should move to next row when add new data as in pic2 and pic3 without repeat calculation amounts have ever sum or subtract( the next file will depend on last amount for previous file) .
the result should be in EMPLOYEE sheet in column D for adjacent cells NAME & NET AMOUNT
I highlighted the changed cells in third picture for each case when change data in TT sheet .
also posted here
sum or subtract for the same cell based on match column between two sheets
case1

1.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-20,000.00
3			SALARY	3,000.00
4			NET AMOUNT	-17,000.00
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	-2,000.00
9			SALARY	3,000.00
10			NET AMOUNT	1,000.00
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	1000
15			SALARY	2500
16			NET AMOUNT	3500
EMPLOYEES

1.xlsm
	A	B	C	D
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2,000.00
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2,500.00
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3,000.00
TT

what I want in EMPLOYEES sheet

1.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-22000
3			SALARY	3000
4			NET AMOUNT	-19000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

Cell Formulas
Range	Formula
D16	D16	=D14+D15

case2

2.xlsm
	A	B	C	D
1	DATE	NAME	DETAILS	AMOUNT
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2,000.00
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2,500.00
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3,000.00
5	14/03/2023	OMAR ALI OMAR	Pay payment bank	5,000.00
TT

what I want in EMPLOYEES sheet based on last updating in CASE1 above

2.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-17000
3			SALARY	3000
4			NET AMOUNT	-14000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

case 3

3.xlsm
	A	B	C	D
1	DATE	NAME	DETAILS	AMOUNT
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2,000.00
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2,500.00
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3,000.00
5	14/03/2023	OMAR ALI OMAR	Pay payment bank	5,000.00
6	14/03/2023	OMAR ALI OMAR	Pay payment bank	2,000.00
7	15/03/2023	ALI MAHMUD OMAR	Pay payment safe	3,200.00
TT

what I want in EMPLOYEES sheet based on last updating in CASE2 above

3.xlsm
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-15000
3			SALARY	3000
4			NET AMOUNT	-12000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	1700
15			SALARY	2500
16			NET AMOUNT	4200
EMPLOYEES

I hope to find answering here for this project

 

[Omran Y]

I appreciate for your, time and help .
just I want last thing . indeed I posted details are not logic . when I run the macro every time will continue subtract or some repeatedly , that's wrong
so I don't find any way to solve that except one thing and it's should add another condition for TT sheet if the date today in column A then will sum or subtract for each name but if there are old dates(not today) then will ignore totally , is the condition possible to add your code?

 

[DanteAmor]

I appreciate for your

With that I understand that the original requirement is covered?

just I want last thing . indeed I posted details are not logic . when I run the macro every time will continue subtract or some repeatedly , that's wrong
so I don't find any way to solve that except one thing and it's should add another condition for TT sheet if the date today in column A then will sum or subtract for each name but if there are old dates(not today) then will ignore totally , is the condition possible to add your code?

I'd be happy to help you, but that condition is not in the OP.
Please create a new thread and explain what you need. Your examples are very good.

 

[Omran Y]

With that I understand that the original requirement is covered?

not completely , sorry !
you don't seem to note the amounts for the some names will keep without any changing again as I posted in OP

when add new data as in pic2 and pic3 without repeat calculation amounts have ever sum or subtract( the next file will depend on last amount for previous file)

doesn't calculate again for the same amounts and the same names have ever calculated
based on OP

Z Microsoft Excel ج.xlsx
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-22000
3			SALARY	3000
4			NET AMOUNT	-19000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

Cell Formulas
Range	Formula
D16	D16	=D14+D15

Z Microsoft Excel.xlsx
	A	B	C	D
1	DATE	NAME	DETAILS	AMOUNT
2	11/02/2023	OMAR ALI OMAR	Advance payment by safe	2000
3	12/02/2023	ALI MAHMUD OMAR	Advance payment by bank	2500
4	13/02/2023	AHMED OMAR ALI	Pay payment safe	3000
5	14/03/2023	OMAR ALI OMAR	Pay payment bank	5000
TT

result

Z Microsoft Excel .xlsx
	A	B	C	D
1	START DATE	LAST DATE	NAME	FIRST BALANCE
2	01/01/2023	31/01/2023	OMAR ALI OMAR	-17000
3			SALARY	3000
4			NET AMOUNT	-14000
5
6
7	START DATE	LAST DATE	NAME	FIRST BALANCE
8	01/01/2023	31/01/2023	AHMED OMAR ALI	1000
9			SALARY	3000
10			NET AMOUNT	4000
11
12
13	START DATE	LAST DATE	NAME	FIRST BALANCE
14	01/01/2023	31/01/2023	ALI MAHMUD OMAR	-1500
15			SALARY	2500
16			NET AMOUNT	1000
EMPLOYEES

as to names AHMED OMAR ALI,ALI MAHMUD OMAR the amounts will keep as the picture1 withoiut change again , you say why?! because it has ever calculated when the data in TT sheet as in picture2 in OP . it's not right when change the amounts again . you can compare the pic 1 with pic3 in this post will keep amounts for names AHMED OMAR ALI,ALI MAHMUD OMAR without any change again(will ignore amounts in row2,3,4 in TT sheet when calculate) , this is the right way and what I want .
so I thought this way is not possible to do that by code . you know more than me . that's why I suggest

it's should add another condition for TT sheet if the date today in column A then will sum or subtract for each name but if there are old dates(not today) then will ignore totally , is the condition possible to add your code?

and of course that condition is not in the OP when I see you don't note this

when add new data as in pic2 and pic3 without repeat calculation amounts have ever sum or subtract( the next file will depend on last amount for previous file)

I suggest for you using DATE in column A

Please create a new thread and explain what you need. Your examples are very good.

if you still this different totally thread then I will add new thread today and I hope to help me to complete this part of my project.
I would your comment soon .
thanks again

 

[Omran Y]

ok I posted new thread
sum or subtract for the same cell based on match DATE