Formula to change cell color when inputted value is not between 2 coloumn values.

I need the cell color to change when the inputted value in the Moisture % column is not between two values, the min or max columns also on the same grid. I am just unsure which formula or automation to utilize for this. The min and max values are pulled from a "master sheet", that way if updates to these levels are needed, I only have to update the master sheet and it auto updates where the master sheet information is pulled from vs having to remember to update individual grids or conditional formatting that is applied to each grid. I am new to smartsheet so still learning the basics and how to best get desired results.

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓


    Add another column [Moisture Error Input].

    It should have a Column Formula:

    =IF(OR([Moisture (%)]@row > [Moisture Max (%)]@row, [Moisture (%)]@row < [Moisture Min (%)]@row), 1, 0)

    Then you can add a conditional formatting, something like the below:


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!