Welcome!

How to link the RYG balls to the Conditional Formatting

Hi there,

We currently have a sheet where we have a formula for RYG balls, Status column, % Complete and Conditional Formatting per row.

We want to remove the Status column and just rely on the % Complete column and Ample column (where the RYG balls are).

If and when the % is not 100% and past the End Date, font for entire row and ball will turn Red.

If and when the % is not 100% and there's only 3 days to go before End Date, font for entire row and ball will turn Yellow.

Everything else is Green.

Here's the formula under the Ample column (row 1).

=IF(Status1 <> 1, IF(TODAY() - [End Date]1 >= 0, "Red", IF(TODAY() - [End Date]1 > -4, "Yellow", "Green")))

Screenshots uploaded as well

Please for your kind help.

Thanks!

Smartsheet Question 1.png

Smartsheet Question.png

Find more posts tagged with

Comments

Mike Wilday

Hi LTS,

To affect the color of the ball, you need to use an IF formula to adjust it in the column where the RYG Ball exists. The following formula should do the trick. Please note that this formula was written for row 24. You can paste it into row 24 and drag it up or down to adjust it for each row. OR you can change the row number at your convenience.

=IF(AND(Today > [End Date24], [% Complete]24 < 1), "Red", IF([% Complete]24 = 1, "Green"))

LTS

Thanks for your reply, Mike.

I copy pasted your formula to my row 24 but I got this error #UNPARSABLE.

I did it again and this time I also deleted the Status column. I still got the error #UNPARSABLE.

Any idea?

Thanks again, appreciate your help.

Mike Wilday

Whoops. I see my mistake.

Try this one.

=IF(AND(Today > [End Date]24, [% Complete]24 < 1), "Red", IF([% Complete]24 = 1, "Green"))

Sign in to join the conversation!

Quick Links

Trending discussions

Why isn't my sheet pulling into a report?
I created a new sheet for my team by replicating an older sheet and now my tasks aren't pulling into my task report or any of my teammates. I confirmed the sheet was selected in my report but it still isn't pulling in. I've never had an issue with this before so would love some advice on troubleshooting.
construction cost estimate for installations
Can someone help me and give me an idea on how to create a cost estimate for installations, with a total for each chapter by groups, subgroups and types of work, but at the bottom of each chapter. https://us.v-cdn.net/6031209/uploads/0OSH46KSC2LH/cost-estimate.xlsx Thanks
Editor-Cannot Share Rights
Hi Everyone, I created some forms for our powdercoat test logs. The forms are working great for the employees to input the data. The problem I am having is with giving the supervisors and managers access to the sheet. I gave them Editor Cannot Share permissions to see and edit the sheet. But they are saying that Smartsheet…