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?

Find more posts tagged with

Accepted answers

David Tutwiler

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

HardWork

You are the best!!! That did the trick, thank you so much!

All comments

David Tutwiler

The formula to this point makes sense and I think would resolve:

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

HardWork

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?

David Tutwiler

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

HardWork

You are the best!!! That did the trick, thank you so much!

David Tutwiler

That's awesome. Glad it's working.

Cimafranca

@David Tutwiler

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

David Tutwiler

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

Cimafranca

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

David Tutwiler

No problem, glad that got it going.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.