Inventory Management
I'm trying to find a way to remove inventory when an order is sold, other than manually changing the numbers. I have a form set up to add new orders to the bottom of the sheet. I need a formula to automatically remove the inventory items from each line in the Sold column. Example:
Customer #1 bought 5 items of my Stock #2. I need it to change the 0 in the Sold column to 5 on line 3.
It also needs to be able to update when another order is added. Example:
Customer #3 bought 35 items from Stock #2. So now the 5 would be changing to 40 (in the Sold column on line 3).
Best Answer
-
Ok. So to pull a total of all items removed for Stock #2...
=SUMIFS(color:color, [WH Location]:[WH Location], [Stock #]@row)
Then we subtract that from your starting stock and that should do it for you.
=[In Stock]@row - SUMIFS(color:color, [WH Location]:[WH Location], [Stock #]@row)
NOTE: This is assuming you are putting the formulas in the [Remaining Stock] column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Where do you determine your starting inventory, or would you be manually entering that into the formula? The formula is very possible by the way. Just need a few quick details worked out first such as the previous question.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Starting inventory would be the "In Stock" column.
-
Ok. So to pull a total of all items removed for Stock #2...
=SUMIFS(color:color, [WH Location]:[WH Location], [Stock #]@row)
Then we subtract that from your starting stock and that should do it for you.
=[In Stock]@row - SUMIFS(color:color, [WH Location]:[WH Location], [Stock #]@row)
NOTE: This is assuming you are putting the formulas in the [Remaining Stock] column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It worked, you're the man!
I'm so excited!
Thank you!
-
Happy to help! 👍️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Got it, thanks again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!