Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Laura Allen
edited 12/09/19 in Archived 2016 Posts

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
    J. Craig Williams ✭✭✭✭✭✭
    edited 04/07/16



    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.





    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.




  • Richard Rymill SBP
    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?


This discussion has been closed.