Feature Request: Conditional formatting based on "or" statements (particularly "is blank")

Laura Allen

The way the conditional formatting rules are currently written:

 

1. You can create condtions that are X or Y when X and Y are values that have already been filled in by click on both from the menu.  

2. You can create chains of "and" statements based on various condtions such as "is blank" or "greater than" both when you elect to "define custom criteria" and by clicking "add condition (AND)"

 

However, you cannot do so for "or."  Forcing the workaround of creating a new rule for each "or"

 

Not only is this time consuming for setup, it also makes the rules list an unweildy nightmare.

 

Currently I a managing a massive spreadsheet where I use conditional formatting to improve accuracy by highlighting cells with perplexing results.  The rules list is already too unweildy, without having to create two rules every time I want: N/A or "is blank"

J. Craig Williams

Laura,

 

I don't know if this will help, but I'll toss it out in case it does.

 

OR = NOT(AND(NOT(arg1),NOT(arg2))

 

Unfortnately the NOT(AND()) part is the sticking point.

You could set the column to the "bad" formatting - N/A or blank and the highlight the good ones.

 

IS NOT BLANK

IS NOT EQUAL TO N/A

 

for the two conditions. 

The good ones will then be highighted by the conditional formatting 'exposing' the bad ones by default.

 

This won't work if there are other rules that impact this column unless you put this formatting after them.

 

Craig

 

Richard Rymill SBP

Nicely put Craig, I would also suggest you explore the use of Reports to identify "Management by exception" Reports. Also to be aware that is your sheet is extremely large and complex it may slow down, so Reports become a good part of the Workflow. 

Hope that helps?

RichardR