How can I automate a subtraction

Hey,

How can I subtract a number from the total each time a row is added. I have an Inventory sheet that linked to another sheet. for example, in the picture below; how do I subtract a Number of Machine each time a row is added?

Thanks in advance.


Best Answer

«1

Answers

  • @ddrame103286 ,

    You can just create a formula for taking the count of all of the rows in a sheet and deduct the count from a set value.

    Hope that help!

    Ryan

  • Thanks Ryan and Paul.

    Yes I want to subtract a number from cell containing "40" each time a row is add to the below location. If possible to subtract a number from different sheet every time a row is added to below location will also be great.


    Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How is the number 40 generated. Is that a manual entry for a starting point?


    Can you clarify what you mean by

    "subtract a number from different sheet every time a row is added to below location"


    Is the row being added to a different sheet or the same sheet as the 40?

    thinkspi.com

  • Hey Paul,

    The number 40 is manually entered. It is from a different sheet. If it is not possible to do the automation from a different sheet then I can manage it on same sheet.


    Thanks again.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It can be done. I just want to make sure I understand where each data piece is coming from.


    Where are the new rows being added?

    thinkspi.com

  • the new rows are added manually for now. and there is another sheet that contains the total inventory ("40").

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    But WHERE are the new rows added? The same sheet as the calculation you want or the other sheet?

    thinkspi.com

  • From other sheet.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So you are staring with the number 40 on a different sheet and you want to subtract 1 for each row that is added to that other sheet. Then you want that number pulled to the sheet shown in your screenshot?


    Sorry for all of the questions. I just want to make sure I completely understand what you are wanting to accomplish.

    thinkspi.com

  • Thanks for your time and appreciated it.

    Sorry for not being clear enough.

    I have two sheets. One is called Machine Sheet, where rows are add and other is called inventory sheet, where total numbers are.

    I want to subtract 1 from inventory sheet each time a row is add to the machine sheet.


    Thanks again.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. That's perfect. Thanks for the clarification, and sorry I was struggling to understand your process.


    So is there some sort of criteria such as a machine type that needs to be taken into account?

    thinkspi.com

  • Yes. We have different types of machines.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So you are going to want to use something along the lines of...


    =40 - COUNTIFS({Machine Sheet Machine Type Column}, "Machine Type")

    thinkspi.com

  • Thanks Paul. Very helpful.