Conditional Formatting Referencing a Cell

Daryl Lee

Hi,

I feel that this is something there isn't functionality for, but I wanted to check and see if I may just be missing something.

Right now, I have a list of a numbers in a column and then have it calculating the average, upper limit (the average + standard deviation), and lower limit (the average - standard deviation).

What I want to do is use conditional formatting to highlight cells in the above column of numbers if it's above the upper limit or below the lower limit. Is there a way to do that? I tried to create the rule, select "is greater than" and then type in the cell number, but that doesn't do anything.

Is there another way to set this up? Or would it have to be manual entry of the number in the conditional formatting and then update that as the upper and lower limit changes?

Paul Newcome

You could include a "helper" column that can be hidden after setup.

You could use a formula to generate a number, letter, or symbol (or even a flag or checkbox if you only have two values) that would be consistent for each conditional format and then build off of the helper column.

Here's an example:

Let's just say for now that you want to set up red for under, yellow for between, and green for over. Your low number is in [Average Column]1 and the high number is in [Average Column]2 (for cell references), and the data you want to compare against is in the [Numbers Column].

You could use a formula in the helper column that looks something along the lines of

=IF([Numbers Column]@row <= $[Average Column]$1, "Red", IF([Numbers Column]@row >= $[Average Column]$2, "Green", "Yellow"))

Then build your Conditional formatting off of whether the helper column displays "Red", "Yellow" or "Green"

Paul Newcome

You could include a "helper" column that can be hidden after setup.

You could use a formula to generate a number, letter, or symbol (or even a flag or checkbox if you only have two values) that would be consistent for each conditional format and then build off of the helper column.

Here's an example:

Let's just say for now that you want to set up red for under, yellow for between, and green for over. Your low number is in [Average Column]1 and the high number is in [Average Column]2 (for cell references), and the data you want to compare against is in the [Numbers Column].

You could use a formula in the helper column that looks something along the lines of

=IF([Numbers Column]@row <= $[Average Column]$1, "Red", IF([Numbers Column]@row >= $[Average Column]$2, "Green", "Yellow"))

Then build your Conditional formatting off of whether the helper column displays "Red", "Yellow" or "Green"

Daryl Lee

Thank you so much, Paul! That worked and did what I needed it to.

Paul Newcome

Happy to help! 👍️

Riyaz Mansoor

Hi Paul.

Would your formula below automatically apply to new rows added to the sheet ?

=IF([Numbers Column]@row <= $[Average Column]$1, "Red", IF([Numbers Column]@row >= $[Average Column]$2, "Green", "Yellow"))

OR would I have to manually copy it to all rows ?

Ps : newbie on Smartsheet.

Andrée Starå

Hi @Riyaz Mansoor

Yes, it would automatically apply to new rows as long as the conditions below are met.

If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

More info: 

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Riyaz Mansoor Yes. The formula should autofill as @Andrée Starå mentioned. The simplified version would be as long as there are two rows above and/or below the new row that contain the formula, then the formula will autofill.

Katherine Nordhues

@Paul Newcome, thank you for your answer! I've been able to make the formula in a helper column to get me almost there. However, I need a third condition for if the value in the first column is EQUAL TO the value in the other cell. So far, my helper column is only showing "green" for values that a greater and "red" for values that are less than, but I need to see "yellow" for values that are equal to.

here is my formula as it is =IF([Proposed Final Goal]@row <= [Campus Goal Avg]@row, "red", IF([Proposed Final Goal]@row >= [Campus Goal Avg]@row, "green", "yellow"))

Thank you for your help!

Paul Newcome

@Katherine Nordhues That is because both of your arguments include the equals function.

Take the "Red" for example...

You WANT "less than", but you have "less than or equal to". Remove the equals signs from both the "red" and the "Green" criteria, and then your "Yellow" should pick up on the equals bit like you are wanting.

enzo_vaire

Hi,

Is there no update on this feature? I am trying to work around the lack of a 'must end by' option by creating an extra column, and it seems strange that such a basic feature as comparing two columns is not available. This is an old thread, is this feature now present?

kajssext

Very much agree with Enzo.