continuous subtraction

lauren garcia

New Member
Joined
Aug 10, 2019
Messages
12
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
 

Some videos you may like

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
Joined
Dec 3, 2018
Messages
12,208
Office Version
2007
Platform
Windows
you can provide some examples
 

lauren garcia

New Member
Joined
Aug 10, 2019
Messages
12
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
Joined
Aug 10, 2019
Messages
12
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
Joined
Aug 10, 2019
Messages
12
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
Joined
Dec 3, 2018
Messages
12,208
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top