continuous subtraction
Results 1 to 7 of 7

Thread: continuous subtraction
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,768
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: continuous subtraction

    you can provide some examples
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: continuous subtraction

    how can i put my screenshot here?

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?
    Last edited by lauren garcia; Aug 14th, 2019 at 11:56 PM. Reason: not correct

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?
    Last edited by lauren garcia; Aug 15th, 2019 at 12:57 AM.

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,768
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: continuous subtraction

    Quote Originally Posted by lauren garcia View Post
    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-...ml#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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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