continuous subtraction

lauren garcia

New Member
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

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

DanteAmor

Well-known Member
you can provide some examples

lauren garcia

New Member
how can i put my screenshot here?

lauren garcia

New Member
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?

Last edited:

lauren garcia

New Member
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?

Last edited:

lauren garcia

New Member
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

Well-known Member
how can i put my screenshot here?

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.

1,102,678
Messages
5,488,223
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...