Conditional Formatting in same column with multi criteria

Elizabeth Aird

I am trying to think my way through this and can't quite manage it yet.

I have a log that has stages but the criteria is based upon the substrate chosen.

If Hot dip is selected, the temperature spec is 120-160.

If Cold Rolled Steel is selected, the temp spec is 120-175.

I need the conditional formatting in the same cell, different specs but to turn red to alert the operators and managers of an "out of spec" condition.

Help appreciated!!

Paul Newcome

If those numbers are not going to change, you can set your conditional formatting to trigger on those specific number.

Another option would be to add in a flag type symbol column and use a formula to flag the row if it is outside of those standards. You can then either just use the flag or build in additional formatting with the Conditional Formatting to further highlight the issue.

Elizabeth Aird

Thanks! I was afraid you were going to say that! There are 18 Stages with 4-5 checks in each stage. That's a lot of hidden columns to make this work.

Paul Newcome

There may be other approaches.

It may be possible to get away with an extra row and 4 additional columns. Can you provide more details and possibly a screenshot of the entire sheet with sensitive/confidential data remove, hidden, or replaced with "dummy data" as needed?

Kelly Gabel

@Paul Newcome So thrilled that you have provided some options for this!! I've been trying to make conditional formatting with multi criteria and it's been making me crazy! 2 questions for you though:

1. Can you share an example of a formula for the flagged field? (eg. if the data in x field is greater than or equal to 0, than use the red flag, etc.)
2. How would you do it with the extra row and 4 columns? Is there are way to use a row in conditional formatting? So far all I've seen is columns.

Paul Newcome

@Kelly Gabel Can you provide a screenshot of what you are working with? Sensitive/confidential data can be blocked, removed, or replaced with "dummy data" as needed.

Kelly Gabel

Screenshot for SS.PNG

@Paul Newcome Here is the screenshot. So the specific situation is I need the conditional formatting to only look at rows with a Data Type of "Delta", then look at column "Jan". If that value is greater than or equal to 0, then make that cell (here -0.-02) green, if it is less than 0, make it red.

Does that make sense? I've started adding in flagged fields per your suggestion above but I am struggling to create a formula that would make the flag red so that I can do the additional formatting.

Paul Newcome

Try this formula in your flag column...

=IF(Jan@row <= 0, 1)

This will flag all rows where the Jan column is less than zero.

Then your conditional formatting can be set up so that if the Data Type column is "Delta" and the Flag column is flagged, "red formatting".

From there you can duplicate the conditional formatting rule, flip the flag criteria, and then update the format to reflect your green.

Kelly Gabel

@Paul Newcome Thank you!!! I can logically see how this will work...Thanks for taking the time!

Kelly Gabel

Screenshot error.PNG

@Paul Newcome It worked great for the Jan column but when I tried to modify for the YE 2019 column I'm getting an error...This is what I did. Can you show me where I went wrong? =IF([YE 2019]9@row <= 0,1)

Additionally, with regards to the conditional formatting, how can you do one conditional format with two criteria? (Data Type and Flagged) I've been trying to figure out how to do that since I started with Smartsheet!

Kelly Gabel

Oh! I just figured out the conditional formatting part, so I just need help now with my formula. @Paul Newcome

Thank you so much!

Paul Newcome

Use just the @row reference. It replaces the row number and basically says to look in that column on whatever row the formula is on. It is a little more efficient on the back-end and helps avoid accidentally entering the wrong row number.

=IF([YE 2019]@row <= 0,1)

Kelly Gabel

Dude...that was what I did first and it didn't work...but of course I copy/pasted yours and it works great! LOL Thanks so much!! @Paul Newcome

Paul Newcome

Haha. Go figure. At least it is working now. Happy to help! 👍️

Kelly Gabel

@Paul Newcome Does this also work if the flag column has three option (like using the stoplight for example)? It seems plausible to me but the formula is probably different (not true/false) I need a third option on the conditional formatting (green, red, no background).

Paul Newcome

To work in a third, you actually have a ton of options. What are your 3 sets of criteria?