Display RYG based on selection from drop down menu /w 3 choices and IF/OR statements

Hey

Trying to select from Column3 dropdown menu (Not Started, In Progress, Completed) to determine Column4 entries as Red (Not Started), Yellow (In Progress), Green (Completed).

Not a programmer but tried: -

=IF(([Column3], “Not Started”, “Red”), IF(([Column3], “In Progress”, “Yellow”), IF(([Column3] “Completed”, “Green”))

Unpareseable = result.

Help please!

Thanks.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Worfed If you copied the formula from @Jeff Reisman's post, then you will get that error because of the quotes.


    See how they are slanted? Those are not recognized by Smartsheet as valid characters in a formula. You will need to retype it directly in the sheet yourself.


    For reference "These quotes" are straight up and down. "These quotes" are the ones that will work in SS. The difference comes from which program you are typing in. Microsoft Word uses the slanted quotes whereas this community forum, Smartsheet itself, and certain text editors such as Notepad use "These quotes".

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Worfed It looks like you have a syntax issue. The syntax for IF is:

    IF( Logical expression, value if true, value if false )

    I think in your case, you put commas inside your logical expressions rather than operators, and you've got extra parentheses in there too. Also need to tell the formula which row you're dealing with. We'll use @row so that the formula will work on any row you put it on. Try this and see how it works for you:

    =IF([Column3]@row = “Not Started”, “Red”, IF([Column3]@row = “In Progress”, “Yellow”, IF([Column3]@row = “Completed”, “Green”)))

    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!

  • Worfed
    Worfed ✭✭

    Hey

    Thanks very much for answering. The explanation was extremely helpful, particularly in terms of syntax and methodology. However, still getting "unparseable"!

    Is it anything to do with the following: -

    1. I've formatted Column4 to Symbol (RYG)
    2. Column3 is Dropdown Multichoice
    3. Column3 Dropdown order is as coded (RYG)
    4. Column3 and Column4 is coded "restrict to Dropdown Values Only"

    Can you think of anything else?

    Thx in anticipation.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Worfed If you copied the formula from @Jeff Reisman's post, then you will get that error because of the quotes.


    See how they are slanted? Those are not recognized by Smartsheet as valid characters in a formula. You will need to retype it directly in the sheet yourself.


    For reference "These quotes" are straight up and down. "These quotes" are the ones that will work in SS. The difference comes from which program you are typing in. Microsoft Word uses the slanted quotes whereas this community forum, Smartsheet itself, and certain text editors such as Notepad use "These quotes".

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Worfed
    Worfed ✭✭

    Woohoo! Thanks Paul - I use TexEdit a Plain Text Editor usually when doing something like this (for any Excel Formulas) - but yes copied and pasted direct here - Jeff's explanation was however extremely helpful regarding Syntax though so nothing wasted - & thanks for heads of on the typographical esoterics of Smartsheet :) Working now can get back to the project admin :)

  • Worfed
    Worfed ✭✭

    @Jeff thanks again for the extremely helpful syntax based explanation :)

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    😂 I copied them from @Worfed 's original formula in his initial post! Those MS Word quotes have stung me before, I should watch out for them. Any time I need to manipulate a formula outside of Smartsheet I usually just use Notepad.

    @Worfed happy I could help with the syntax stuff!

    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!

  • Worfed
    Worfed ✭✭

    @Jeff Reisman hey Jeff guess its a cut and paste conversion issue (forum not you or me) as my original quotes were cut and pasted from a Mac Plain Text Text Editor (TextEdit). But appreciate input from both of you - many thanks!

    Next project to Automate %ge progress entry to trigger the status (Not started/In progress/Completed) and thence the RYG buttons - will save considerable time in the long project I'm working on (103 Rows).

    Regards.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jeff Reisman Haha. I have had the same exact thing happen to me.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!