How to Trigger Formatting from Comparison / Nested IF?

Aileen@RoyalCanin

Hello!

I have project sheet, with dependencies enabled.  This requires manually updating the % Complete value.  I would like to add conditional formatting to remind myself to update the % Complete.  

IF [Completed]1 is not blank

and

IF [% Complete]1 does not equal 100%

Then 

Format [% Complete]1 Cell Fill Yellow

(screenshot attached)

Any suggestions, Fabulous Community Folks?

 

Aileen@RoyalCanin

Hi Gang,

Well, I played around a bit, and found this approach:

1. Create a new column called "Update Reminder"

2. Use the following formula:

=IF(NOT(ISBLANK(Completed4)), IF([% Complete]4 < 1, "Update", "Do Not Update"))

3. Set Conditional Formatting based on "Update Reminder" field.  If Update Reminder = Update, then fill cell green.

What do you think?

Robert S.

Hello,

 

This is a great way to make this work, however it's also possible to make this work without another column and the formula and only using Conditional Formatting. You can set multiple conditions for a Conditional Formatting rule, so that a row has to meet all of the conditions before applying the formatting.

 

To do this, you can create the conditional formatting rule as you did before and for the first condition, set it to "If Completed is not blank". You can then add another condition by clicking the downward facing arrow to the right of the rule, and choosing "Add Condition (AND)". This creates another condition that you can then set to be "If % Complete is not equal to 1". You can then set the rest of the rule how you did previously.

 

When it's set up like this, the rule should read "If Completed is not blank and % Complete is not equal to 1 then apply this format to the % Complete column".

 

Both ways get to the same end result, however this method will be slightly less cumbersome if the sheet grows to be large. Hope this helps.