Inventory management. Is there a way to remove the manual removal/addition of component counts?

Jtbeck

I have a Smartsheet inventory log for multiple projects. To make a complete build, it may require 1-5 of each component. I am wondering if there is a way to create a sheet with these pre-determined component amounts needed for one build, input the number of builds required and then have that communicate to the inventory sheet to remove the respective components? Also, for adding components, if I have a purchase order tracking sheet, is there a way to link this sheet and create a formula to add the quantity on the PO once it is received and verified?

Cory Page

I dont think what you're looking for is impossible but I think we may require some screenshots of some dummy data. I am concerned that the removal of components automatically may be a bit tricky but with good example data I am sure we can give it a try..

Paul Newcome

Yes. Your first formula would require a column that indicates how many builds there are and another column to input the pre-determined number. From there you would multiply them together.

The second would be a SUMIFS type of formula with cross sheet references to the PO tracker.

Jtbeck

Thank you for the feedback! I will attempt to create a form that includes build amount and a formula to subtract certain components from each row impacted.

Jtbeck

Sample sheet.png

Uploaded Jul 25, 202312.83 KB

Here is a sample of an inventory tracking sheet. Let's say we had a build of 5. To build 1 it would take the following:

Component 1: 3

Component 2: 1

Component 3: 7

Component 4: 10

Component 5: 1

Component 6: 17

I am wondering if I can enter a formula within the "build qty" column to subtract from the "quantity on hand" column from each component row per the amount needed in the outline listed above.

Paul Newcome

You can. I would recommend another column that houses how many are needed for a single build. Then you would need some way of figuring out how many builds you have. I will assume a COUNTIFS to a separate sheet for now. Then multiply that by the number needed for a single build and subtract it from the On Hand column.

=[Starting Inventory]@row - (COUNTIFS({Job Sheet Jobs}..............) * [Single Build Qty Column]@row) + SUMIFS({PO Sheet Qty Column}, {PO Sheet Component Column}, @cell = [Primary Column]@row)