sheet 1
A B C D E F G H
SITE Article Material Description Name Sum of Cumul. confirmed qty2 QTY. available qty. needs to be delivered qty. needs to fill in
2100 t-shirt jomar 100
2100 t-shirt lea 50
2200 shorts yen 200
2200 shorts nelisa 100
2200 shorts james 100
sheet 2
A B C D
article description site stocks available
2100 t-shirt 1100 400
2100 t-shirt 1200 250
2200 shorts 1100 300
2200 shorts 1300 300
2200 shorts 1400 100
in my sheet 1 is my template for my back orders and in sheet 2 is my template for stocks
i have a formula in sheet 1 row F that will match automatically the qty. available that is in sheet 2, my formula is =IF(COUNT(A2),MIN(INDEX(stocks!$E$6:E2000,MATCH(1,('7-26-19'!A2=stocks!$C$6:C2000)*('7-26-19'!B2=stocks!$A$6:A2000),0))),"")
what i want to get is the qty. available in sheet 2 will subtract automatically when input a correspondent site to the customer
for example:
A B C D E F G H
SITE Article Material Description Name Sum of Cumul. confirmed qty2 QTY. available qty. needs to be delivered qty. needs to fill in
1100 2100 t-shirt jomar 100 400 100 -
1100 2100 t-shirt lea 50 300 50 -
2200 shorts yen 200
2200 shorts nelisa 100
2200 shorts james 100
sheet 2
A B C D
article description site stocks available
2100 t-shirt 1100 250
2100 t-shirt 1200 250
2200 shorts 1100 300
2200 shorts 1300 300
2200 shorts 1400 100
what formula that i need?