How to link the RYG balls to the Conditional Formatting

LTS

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!

 

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"))