Assigning numeric values to drop down menu options

jtlsf1
jtlsf1
edited 12/09/19 in Formulas and Functions

I want to assign a unique numeric value to each of the options in a drop down menu column, and have that numeric value display in an adjacent cell.

 

Drop down menu options in each cell are:

Needs Contributor

Contributor Reviewing

Ready for Production 

 

If 'Needs Contributor,' is chosen, then adjacent cell is populated '1'

If 'Contributor Reviewing,' is chosen, then adjacent cell is populated '2'

If 'Ready for Production,' is chosen, then adjacent cell is populated '3'

 

Is there a formula for this kind of thing?

 

Thanks.

 

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Yes, absolutely! Try this IF statement in the column you created adjacent to the dropdown. Be sure to update Dropdown Column Name with your actual Dropdown column name. 

    =IF([Dropdown  Column Name]@row = "Needs Contributor", 1, IF([Dropdown  Column Name]@row = "Contributor Reviewing", 2, IF([Dropdown  Column Name]@row = "Ready for Production", 3)))

  • Thanks Mike for responding, I appreciate it. It worked! Then I added a few more options, and expanded the formula you provided, but I must be doing something wrong because I get the #UNPARSEABLE message. This is what I entered:

    =IF([Content Status]@row = “MVP Content", 1, IF([Content Status]@row = "Needs Contributor", 2, IF([Content Status]@row = "Contributor Reviewing", 3, IF([Content Status]@row = "Ready for Production", 4, IF([Content Status]@row = "Ready for Contentful", 5, IF([Content Status]@row = "Posted in Contentful", 6)))

    Do I need to add more closed parentheses at the end? I tried that and it didn't seem to work.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    There was an error with one (the first one) of the " was “. Easy to miss! How did you edit the formula?

    The closing parenthesis will correct itself for this formula.

    Try this one.

    =IF([Content Status]@row = "MVP Content", 1, IF([Content Status]@row = "Needs Contributor", 2, IF([Content Status]@row = "Contributor Reviewing", 3, IF([Content Status]@row = "Ready for Production", 4, IF([Content Status]@row = "Ready for Contentful", 5, IF([Content Status]@row = "Posted in Contentful", 6)))

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Still need a further 3 ))) at the end of this example!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Upon review, yes you need more closing parenthesis. Its kind of tedious to count them out but you have to one for each IF( statement. Smartsheet has a little known helper though. If you delete all of the closing parenthesis at the end of your formula, it will add them for you! Give it a try. 

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Ah yes, Andree pointed out the first quotation mark is a slanted quotation mark. if you try to edit your formulas in a word processor it will substitute a smart quote for a regular quote. Always use wordpad or another Simple Text tool like notepad++ or Atom for Mac to edit and compose your formulas. 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!