Equation to populate new number
Hello. I have a sheet with 4 columns: one is the name of the material (Material Name), the next is the quantity of that material that we have in stock (Quantity), the next one is who claimed this material (Claimed By), and then the last column is how much quantity that person claimed (Quantity Claimed). So for example, if we have 1000 feet of wire left over, someone can go and claim 500 feet of it.
I am looking for an equation or formula or just a way so that when someone puts a number in the "Quantity Claimed" column, it automatically subtracts that number from the "Quantity" column so the number in the "Quantity" is updated. Seems easy enough but I can't figure it out.
Thank you!
Best Answer
-
So you're going to need a helper column for Quantity. This column should contain the "Starting Quantity," and the current Quantity column could become "Available Quantity".
In the Available Quantity column use an IF formula.
=IF(ISNUMBER([Quantity Claimed]@row), [Starting Quantity]@row - [Quantity Claimed]@row, [Starting Quantity]@row)
In English: If there is a number value in Quantity Claimed, subtract that value from the Starting Quantity, otherwise, just show the Starting Quantity in this cell.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
So you're going to need a helper column for Quantity. This column should contain the "Starting Quantity," and the current Quantity column could become "Available Quantity".
In the Available Quantity column use an IF formula.
=IF(ISNUMBER([Quantity Claimed]@row), [Starting Quantity]@row - [Quantity Claimed]@row, [Starting Quantity]@row)
In English: If there is a number value in Quantity Claimed, subtract that value from the Starting Quantity, otherwise, just show the Starting Quantity in this cell.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you Jeff! That worked perfectly
-
Any ideas for an organized way to display multiple people who claim different quantities of the same material?
-
I don't think it's dynamic enough to do that. You might be able to but it would be really complicated and I just don't have time to figure out the logic. You'd likely need one or more helper sheets too.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!