Inventory management. Is there a way to remove the manual removal/addition of component counts?
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?
Answers
-
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..
-
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.
-
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.
-
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.
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!