Conditional Formatting

I was wondering if there is a way to apply conditional formatting on a cell based on the outcome of another cell. For instance, I have a % Complete Calculated column and a % Complete Actual column. Is there a way to change a cell in the % Complete Actual column yellow if it is less than the % Complete Calculated?

Find more posts tagged with

Comments

Boomba

Yes. Conditional Formatting is a really valuable feature in Smartsheet! You can find it in the left toolbar below the font tools.

Here is a good article on how to use it: http://help.smartsheet.com/articles/516359-conditional-formatting

On my project mangement sheets, I use Conditional Formatting to change all the text of completed projects to 50% grey. This way, the current projects show up in eye-catching black text, and the finished projects are greyed out. This way you can still see the finished projects, but they don't diminish from focusing on the current tasks.

Laurie Bearden

Thank you for your comment. I actually use Conditional Formatting on a number of cells already, but I'm at a loss on this particular format that I'm trying to incorporate. I don't know if I explained it right. I've attached a screenshot to try to explain better. I want the "% Complete Actual" column to display yellow if it's less than the "% Complete" column. Do you know if that is possible?

Laurie Bearden

Let me try that again. I'm not sure if you can read that, but hopefully it gets the idea across.

Boomba

Ah, you just want that one cell to change. I see.

Go to Conditional Formatting, and create a New Rule.

In the first box, choose the "% Complete" column.

Click the blue text link above the second box, "select from list".

Choose "is less than" in the first box, and "1" in the second box. (Anything less than 100% is less than 1.)

Click the OK button.

Click the blue text "this format", and choose how you want these cells to appear. (I chose a yellow background fill for the cell.)

Click the "entire row" blue text, and choose "% Complete" from the list. Click OK.

Click the OK button, and now any value in the % Complete column that is less than 100% will have a yellow background in that cell.

Like this:

Screen Shot 2016-09-27 at 11.37.51 AM.png

Laurie Bearden

Thank you, but unfortunately that's not working how I need it to. The %Complete column has a formula based on other cells, but the % Complete Actual column does not (I don't know if that matters). Anyway, I want the % Complete Actual column to turn yellow based on the number in the % Complete column. So if the % Complete column is 5% and the % Complete Actual column is 2%, I want that to turn yellow. However, if the % Complete column is 5% and the % Complete Actual column is 10%, the yellow would not apply.

Laurie Bearden

I decided to create a hidden cell with a formula. That has completely solved my problem. Thank you for your help.

J. Craig Williams

Laurie,

Your solution is the only way to do a (cell value) > (another cell value) as Conditional Formatting won't do it directly.

Craig

Nicole

Hi Laurie - I know this a from a few years ago - but with that hidden column, how did you create the conditional formatting? I'm trying to do something similar with dates and I'm at a loss. Your situation seems close to what I want to accomplish.

Laurie Bearden

Hi Nicole. I've attached a screenshot of the conditional formatting rules that I created and the hidden row of what it applies to. The row is titled % Complete Actual / % Time Consumed. Each of those is it's own row as well as the hidden row. I have a lot of formulas in this worksheet, which I found was the best way to get the results I was after. I hope this helps.

Conditional Formatting Discussion.png

Nicole

Thank you! I am going to try this on my sheet to see if this will work!