Red, Yellow, Green, Blue

HardWork
HardWork ✭✭
edited 01/17/22 in Formulas and Functions

=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

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    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
    HardWork ✭✭
    Answer ✓

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

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    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?

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    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
    HardWork ✭✭
    Answer ✓

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

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    That's awesome. Glad it's working.

  • Cimafranca
    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
    David Tutwiler Overachievers Alumni

    @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
    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
    David Tutwiler Overachievers Alumni

    No problem, glad that got it going.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!