How can I automate a subtraction

ddrame103286

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.

Paul Newcome

Are you wanting to put it in the cell containing the "40"? Are you only wanting to subtract new rows added below "Location"?

Ryan Kramer

@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

Paul Newcome

Are you wanting to put it in the cell containing the "40"? Are you only wanting to subtract new rows added below "Location"?

ddrame103286

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

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?

ddrame103286

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

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?

ddrame103286

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

Paul Newcome

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

ddrame103286

From other sheet.

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.

ddrame103286

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

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?

ddrame103286

Yes. We have different types of machines.

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")

ddrame103286

Thanks Paul. Very helpful.