Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula to add or subtract value based on item selected in drop down list
Hi folks,
I would like to create a formula that can identify the item selceted in a drop down column and then automatically add a value based on the item selected in the drop down to the next cell.
Example if the item i selected from the dropdown list in column a was "deduct 10 points" I would like the formula to automatically deduct 10 points from the column b in the same row.
Any help is appreciated.
Thanks
Jon
Comments
-
Jon,
Depending on the number of choices, you'll like want to build a formula using:
1. Nested IF's
2. VALUE() function
3. FIND() and/or various combinations of LEFT(), RIGHT()
Easist would probably be one formula to determine the value and then use the value in the column, but this is only for troubleshooting later.
Best practice would be to keep as consistent text in your dropdown as possible.
"deduct" instead of "Deduct", "deduct", and "subtract". More important for shorter formula would be to pad the numbers "deduct 05" instead of "deduct 5".
Lastly, you might take a short cut by changing the drop down to
a. deduct 10 points
b. deduct 5 points
c. add 5 points
d. add 10 points
or even
-10: deduct 10 points
-5: deduct 5 points
etc...
but that depends on how many items in your list and preference on how to display it.
The first applies to shorter lists.
Hope that helps
Craig
