Thread: continuous subtraction Thanks: 0 Likes: 0

1. continuous subtraction

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

2. Re: continuous subtraction

you can provide some examples

3. Re: continuous subtraction

how can i put my screenshot here?

4. Re: continuous subtraction

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?

5. Re: continuous subtraction

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?

6. Re: continuous subtraction

https://docs.zoho.com/sheet/open/fzu...heets/template

that is my template

https://docs.zoho.com/sheet/open/fzu...ocks/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

7. Re: continuous subtraction

Originally Posted by lauren garcia
how can i put my screenshot here?
- Want to help your helpers by posting a small, copyable, screen shot directly in your post?

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.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•