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.
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?
Comments
-
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.
-
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?
-
-
Let me try that again. I'm not sure if you can read that, but hopefully it gets the idea across.
-
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:
-
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.
-
I decided to create a hidden cell with a formula. That has completely solved my problem. Thank you for your help.
-
Laurie,
Your solution is the only way to do a (cell value) > (another cell value) as Conditional Formatting won't do it directly.
Craig
-
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.
-
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.
-
Thank you! I am going to try this on my sheet to see if this will work!
-
That worked! Thank you so much for that workaround
