Setting Conditional Formatting/Formulae(?) for Red Yellow Green balls based on % Completed

Steve A.

Hello,

 

I am trying to have the Red, Yellow, and Green status balls automatically change/update based on the percent completion of each row task. When I click on the conditional formatting button in navigation pane and make new rule of, "If % Complete is between range of 1% and 99%, then change the status column to Yellow" the change actually makes the entire cell yellow, but leaves the balls unchanged... Additionally I think I would create a rule of, "if % Complete is equal to 100%, then change the status column ball to Green", but ideally this would change that little colored ball, NOT the entire Status Cell.  

 

Hopefully there's a way to do this as using "% complete" AND the "Red, Yellow, Green" balls in status columns become therefore redundant and not very satisfying upon task completion due to the status columns for R,Y,G being only manually updatable.

 

Thank you for any input! 

-Steve 

Greg Gates

You can absolutely do this! It may be a little counter-intuitive, since the RGY status balls are all about color, but you would actually implement this using a regular IF-statement inside of your RGY cells. First, I'd remove the conditional formatting rules you set up. Then, put something like the following formula into the RGY cells (I'm assuming your Percentage column is called "Percent"):

 

=IF(Percent1 < 1, "Yellow", "Green")

 

Even though your formula is technically just outputting the word "yellow" or the word "green," Smartsheet will automatically replace the word with the appropriately colored ball. I hope that helps! 

 

Helene Delgado

Is anyone else having trouble with the Green ball not showing up in Internet Explorer?

 

 

lauram31036

Hi,

Is it possible to use this formula using a date instead of a %?

I have a column for completed date, once there is a date in this column, I would like the status circle to automatically become green. Similar to the example above I managed to use conditional formatting to highlight the status cell as green but would prefer the circle colour to change.

Thanks for any input!

J. Craig Williams

This should work:

=IF(ISBLANK([Completed Date]23), "", "Green")

for a column named [Completed Date] and row 23.

It will be blank until there is a date in that cell, then it will be Green.

Craig