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

Worfed

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.

Paul Newcome

@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".

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”)))

Worfed

Hey

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

SSheet Screenie 16.5.22.png

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

@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".

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

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/329713#Comment_329713

😂 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!

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

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