Want to have status symbols change based on the value of a cell.

Greetings,

I have two columns, one to indicate Project Progress, and one to display the progress as a symbol. I want to create a formula to connect the Project Progress dropdown options with each of the Harvey ball symbols and came up with the formula below. Each individual formula works, but when combining the formulas, it shows an #UNPARSABLE

=IF([Project Progress]@row= "0-25% - Not Started/very little progress", "Empty")

=IF([Project Progress]@row= "25-50% - Some progress made", "Quarter")

=IF([Project Progress]@row= "51-75% - Significant progress made", "Half")

=IF([Project Progress]@row= "0-25% - 76-99% - Nearly complete/submitted to communications", "Three Quarter")

=IF([Project Progress]@row= "100% - Published", "Full")

The combined formula is:

=IF([Project Progress]@row= "0-25% - Not Started/very little progress", "Empty"), IF([Project Progress]@row= "25-50% - Some progress made", "Quarter"), IF([Project Progress]@row= "51-75% - Significant progress made", "Half"), IF([Project Progress]@row= "0-25% - 76-99% - Nearly complete/submitted to communications", "Three Quarter"), IF([Project Progress]@row= "100% - Published", "Full")

Best Answer

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭
    Answer ✓

    Hello @leannanemeth

    You're having the error because you got excess closing parenthesis.

    To fix your formula, try this:

    =IF([Project Progress]@row= "0-25% - Not Started/very little progress", "Empty", IF([Project Progress]@row= "25-50% - Some progress made", "Quarter", IF([Project Progress]@row= "51-75% - Significant progress made", "Half", IF([Project Progress]@row= "0-25% - 76-99% - Nearly complete/submitted to communications", "Three Quarter", IF([Project Progress]@row= "100% - Published", "Full")))))

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

Answers

  • Just noting that I did remove the "Restrict to symbol values only" from the project status column!

  • Melissa Yamada
    Melissa Yamada ✭✭✭✭✭
    Answer ✓

    Hello @leannanemeth

    You're having the error because you got excess closing parenthesis.

    To fix your formula, try this:

    =IF([Project Progress]@row= "0-25% - Not Started/very little progress", "Empty", IF([Project Progress]@row= "25-50% - Some progress made", "Quarter", IF([Project Progress]@row= "51-75% - Significant progress made", "Half", IF([Project Progress]@row= "0-25% - 76-99% - Nearly complete/submitted to communications", "Three Quarter", IF([Project Progress]@row= "100% - Published", "Full")))))

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

  • Thank you so much! I got it to work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!