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!