IF Statement to Display Symbols

Formula variable question for an IF Statement. Ok I have 5 columns (see screenshot below). This formula will be added to the one on the far right (Certification Complete). Now I would like to assign a value to each to trigger the symbol that will display. Basically the way it works is that all 5 columns are required, if "Revoke" is selected for "Certify". If that is the scale, CF highlights the "Comments" cell telling the user that this is required.

I want a completed record (5 columns with Revoke, or 4 columns with Review, as Comments are only required for Revoke). This should display Green.

If no entries have been made, it should display Red.

If partial entries have been made, it should display Yellow.

Sherry Fox

Business Process Analyst 3 | C5ISR Group

HII | Mission Technologies

EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

Connect with me on LinkedIn

Tags:

Best Answer

  • dojones
    dojones ✭✭✭✭✭
    Answer ✓

    Try this.

    =IF(AND(ISBLANK(Certify@row), ISBLANK([User Name]@row), ISBLANK(Group@row)), "Red", IF(AND(Certify@row = "Revoke", OR(ISBLANK(Comments@row), ISBLANK([User Name]@row), ISBLANK(Group@row))), "Yellow", IF(OR(ISBLANK(Certify@row), ISBLANK([User Name]@row), ISBLANK(Group@row)), "Yellow", "Green")))

Answers

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭

    Hi @Sherry Fox

    Something like this perhaps?

    =IF(AND([Certify]@row = "Revoke", ISBLANK([Comments]@row)), "Red",
    IF(AND([Certify]@row = "Revoke", NOT(ISBLANK([Comments]@row))), "Green",
    IF(OR([Certify]@row = "Review", [Certify]@row = "Revoke", NOT(ISBLANK([User Name]@row)), NOT(ISBLANK([Group]@row))), "Yellow", "Red")))

    https://www.linkedin.com/in/zchrispalmer/

  • dojones
    dojones ✭✭✭✭✭
    Answer ✓

    Try this.

    =IF(AND(ISBLANK(Certify@row), ISBLANK([User Name]@row), ISBLANK(Group@row)), "Red", IF(AND(Certify@row = "Revoke", OR(ISBLANK(Comments@row), ISBLANK([User Name]@row), ISBLANK(Group@row))), "Yellow", IF(OR(ISBLANK(Certify@row), ISBLANK([User Name]@row), ISBLANK(Group@row)), "Yellow", "Green")))

  • SherryFox
    SherryFox ✭✭✭
    edited 11/21/24

    Chris,

    That did not quite work.

    • The top row shows no entries in each column, and the symbol is RED, this is correct.
    • The 2nd rows has ONE column completed, where all others are left blank (comments is NOT required here, as the Certify is set to Approve), this should be YELLOW.
      • This shows currently as Red and needs to be Yellow.
    • The 3rd row has all columns filled in, (comments is NOT required here, as the Certify is set to Approve), this should be GREEN).
      • This shows currently as Yellow and needs to be Green.
    • The 4th column has all columns filled in. Certify is set to revoke, so comments is highlighted, and filled out. This row should be GREEN and is. This is correct.

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group

    HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

  • SherryFox
    SherryFox ✭✭✭

    @dojones ,

    That works perfectly!!!! I was obviously overcomplicating it as a result of the Certify column. Thanks so much for your help in resolving this!

    @=Chris Palmer ,

    I wanted to thank you for your help too!!!

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group

    HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!