Formula Creation l Conditional Formatting (Automating the Process)

Wydrikus

Hi!

I am attempting to generate a formula to mimic the one I created using conditional formatting (essentially, I would like to automate the conditional formatting trigger).

Here's how I would like the sheet to work. 

Based on the "Risk Classification" drop down selected, I would like the sheet to pre-fill the "Periodic Review Needed" column with a yes or no. The conditions are based on the year populated for the  "Last Due Diligence Review" and the "Risk Classification" drop.

If the risk classification is "Confidential Information and Tactical (2-years)", any year older than 2018 (in the Last Due Diligence Review column) should populate the "Periodic Review Needed" column with yes. If it is 2018 or 2019, the column should populate no.

Additionally, for any Yes's I would like to highlight the entire row

Is this something that is achievable?

Thank you in advance for your help!

 

 

Brian Campbell

Hello,

I played around with this and believe I came up with the formula.  Place this in the Periodic Review Needed column and it should calculate yes or no.  Then you should be able to adjust the condition to be if "Periodic Review Needed" is yes highlight the row.

=IF(AND([Risk Classification]@row = "High Risk Continuity Critical (Annual)", [Last Due Diligence]@row <= (YEAR(TODAY()) - 1)), "Yes", IF(AND([Risk Classification]@row = "Confidential Information and Tactical (2-years)", [Last Due Diligence]@row <= (YEAR(TODAY()) - 2)), "Yes", IF(AND([Risk Classification]@row = "Physical Access & Liability (3-years or Renewal)", [Last Due Diligence]@row <= (YEAR(TODAY()) - 3)), "Yes", "No")))