Red, Yellow, Green, Blue
=IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", ISBLANK([CEO Approval to Close]@row = " ", "Blue", ISBLANK([CEO Approval to Re-Open]@row = " ", "Blue"))))))
Stage 1 - Request to Close
Approved - Red
Declined - Yellow
Blank - Blue
Stage 2 - Request to Open
Approved - Green
Declined - Red
Blank - Blue
But my formula is giving me an error message. Both stages are on the same row. Stage 2 trumps Stage 1. What am I doing wrong?
Best Answers
-
Gotcha. I missed that you had ISBLANK and = " ". You don't need both because ISBLANK will return true if it is blank or false if it is not. This formula should resolve all of your scenarios.
=IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", IF(ISBLANK([CEO Approval to Close]@row), "Blue", IF(ISBLANK([CEO Approval to Re-Open]@row), "Blue"))))))
-
You are the best!!! That did the trick, thank you so much!
Answers
-
The formula to this point makes sense and I think would resolve:
=IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red"
However, after that you move into ISBLANK, which will either return True or False. I think what you want to do is put an IF(ISBLANK( and follow that scheme through the end of your formula. That should make it resolve without error. So something like:
=IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", IF(ISBLANK([CEO Approval to Close]@row = " ", "Blue", IF(ISBLANK([CEO Approval to Re-Open]@row = " ", "Blue"))))))))
-
Thank you so much David for helping.
I got an "Incorrect Argument Set" error message with your:
=IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", IF(ISBLANK([CEO Approval to Close]@row = " ", "Blue", IF(ISBLANK([CEO Approval to Re-Open]@row = " ", "Blue"))))))))
Any other thoughts?
-
Gotcha. I missed that you had ISBLANK and = " ". You don't need both because ISBLANK will return true if it is blank or false if it is not. This formula should resolve all of your scenarios.
=IF([CEO Approval to Re-Open]@row = "Approved", "Green", IF([CEO Approval to Re-Open]@row = "Declined", "Red", IF([CEO Approval to Close]@row = "Declined", "Yellow", IF([CEO Approval to Close]@row = "Approved", "Red", IF(ISBLANK([CEO Approval to Close]@row), "Blue", IF(ISBLANK([CEO Approval to Re-Open]@row), "Blue"))))))
-
You are the best!!! That did the trick, thank you so much!
-
That's awesome. Glad it's working.
-
I have an even more basic question regarding Green, Yellow, and Red. I thought typing any of these color descriptors in parentheses would yield the corresponding color icon for IF functions. While I can get my IF function to behave properly, the formula is returning the actual word Green, Yellow, or Red. How do I get this IF function to return the appropriately colored dot? And the Column Property needs to be set to Text/Number, correct?
=IF(Status@row = "Completed", "Green", IF([Due Date]@row < TODAY(7), "Yellow", IF([Due Date]@row > TODAY(0), "Red")))
-
@Cimafranca The good news is that the formula looks correct. I believe your issue is with the column property. For this to work you have to set the column up to use Symbols, and then select the Red/Green/Yellow bubbles as your symbol choice.
Otherwise, you are just returning the words Red/Green/Yellow and Smartsheet will put those words in the cell instead. Setting the column type to Symbols lets Smartsheet know that you want to convert the text to the appropriate symbol.
-
@David Tutwiler Yes - your recommendation fixed it! From the tutorials, I was under the impression that formulas only worked in Text/Number format. I now see the appropriate color dot. Thank you so much for your help!
-
No problem, glad that got it going.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!