Looking for a means of keeping an updated "automatic" inventory
Is there a formula I could use that would:
1.) When "Complete?" is checked on the right side…
2.) Subtract the "Units Requested" with respect to "Lot" from the left side and populate the "Remaining Inventory"
The idea is to have a master inventory sheet that will show remaining inventories while having a secondary sheet that will serve as a place for all inventory requests using a form.
Answers
-
=[Initial Inventory]@row - SUMIFS({Units Requested}, {Lot}, Lot@row, {Complete},1)
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
Hi!
You could use a SUMIFs formula there, and reference the necessary columns from your secondary sheet.
It would look something like:
=[Initial Inventory]@row - SUMIFS({Units Requested column from your secondary sheet}, {Lot column from your secondary sheet}, Lot@row, {Complete? Column from your secondary sheet}, 1)
A slight variation: you could consider adding a column on your Master Inventory for "Completed requests" and put the SUMIFS formula in that column. Then, just subtract the "Completed requests" total from the initial inventory. Same result, but you'll see the numbers more clearly and can make sure the formula is working as intended. :)
Hope that helps!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!