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
-
@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!
Answers
-
@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!
-
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: -
- I've formatted Column4 to Symbol (RYG)
- Column3 is Dropdown Multichoice
- Column3 Dropdown order is as coded (RYG)
- Column3 and Column4 is coded "restrict to Dropdown Values Only"
Can you think of anything else?
Thx in anticipation.
-
@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!
-
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 :)
-
@Jeff thanks again for the extremely helpful syntax based explanation :)
-
😂 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!
-
@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.
-
@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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!