IF Statement to Display Symbols

SherryFox

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.

dojones

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

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

dojones

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

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.

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