#### 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.

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

edited 12/09/19

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

• ✭✭✭✭✭

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!

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

• 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!

• ✭✭✭✭✭✭

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

This discussion has been closed.