continuous subtraction

[lauren garcia]

Hi, how to subtract continuously but the first result will not change

for example
row F2 will minus all the row in G2 that have match and the result will come up to row H2,H3 respectively and I will subtract again row F2 to row H3 and the result will come up to row H3 but the first result that is in row G2 will not change.


thank you

 

[DanteAmor]

you can provide some examples

 

[lauren garcia]

how can i put my screenshot here?

 

[lauren garcia]

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?

 

[lauren garcia]

sheet 1
A B C D E F G H
SITE Article 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 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?

 

[lauren garcia]

https://docs.zoho.com/sheet/open/fzuh970e4ffa3650a4eaa81ede5f4c71d08e2/sheets/template

that is my template

https://docs.zoho.com/sheet/open/fzuh970e4ffa3650a4eaa81ede5f4c71d08e2/sheets/stocks/ranges/E3

and that is my sheet for my stocks

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

 

[DanteAmor]

how can i put my screenshot here?

- Want to help your helpers by posting a small, copyable, screen shot directly in your post?

https://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729



I would prefer if you upload your files to dropbox.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

In the file explain your example and the expected result.