Parent RYB Status Ball - Changing color based on child status RYB ball

Kelly Buse

Hello,

Another request for information!

I have a parent status ball that I would like to change color based on the 2 deadline status balls and the dominant completion ball (i.e. row 1 & row 2 - then row 3 being the dominant ball). The colors are as follows:

• Row 1 & Row 2 - They will be green, yellow or red.
• Row 3 - This will either be green (in progress) or gray (complete)

So, it will change to yellow if any of the two balls are yellow (dominating green) and then red if any of the two balls are red (dominating green and yellow). Then, final color! Once Row 3 is changed to gray - they will override all other colors and change the parent RYB ball to gray (meaning complete).

I hope this makes a slight bit of sense! Thank you ever so much for your help on this request!!!

Paul Newcome

Try something like this...

=IF([Column Name]3 = "Gray", "Gray", IF(OR([Column Name]1 = "Red", [Column Name]2 = "Red"), "Red", IF(OR([Column Name]1 = "Yellow", [Column Name]2 = "Yellow"), "Yellow", "Green")))

Kelly Buse

Hi Paul,

That unfortunately didn't seem to work. Thank you so much for the suggestion though!!

Paul Newcome

Did it throw an error or did it provide an unexpected result? Can you provide more detail as to why exactly it didn't work?

Kelly Buse

Hi Paul,

Thanks for the follow up, Paul. The icon didn't change from green when I changed the color of the child icons between yellow and red etc.

Any suggestions? I've shared a photo of the layout to provide a reference!

Thanks again for your help!

Paul Newcome

It may be a row reference issue. In the screenshot you provided... What are the row numbers?

Kelly Buse

So, I have the following formula that has my spreadsheet values substituted in:

=IF(Australia8 = "Gray", "Gray", IF(OR(Australia6 = "Red", Australia7 = "Red"), "Red", IF(OR(Australia6 = "Yellow", Australia7 = "Yellow"), "Yellow", "Green")))

So, Australia6 = 30-Dec-2019, Australia7 = 23-Dec-2019 & Australia8 = 30-Dec-2019.

I've been playing around for ages with various versions and haven't had any luck! You're help on this is greatly appreciated! Thanks, Kelly.

Paul Newcome

That formula SHOULD be working then. Hmm....

Click on a cell in that column then click on the small dropdown arrow so that you can see the different options. Make sure we are using the exact text strings required.

If your yellow ball says "Amber" next to it, then we would need to use "Amber" in the formula. Same for the other colors. The text has to be an exact match.

DO you have any filters enabled? Is it possible that (for example) what appears to be row 7 is actually row 12?

Kelly Buse

Hi Paul,

Colors are named correctly in the formula and no filters have been applied. This is weird odd!

Paul Newcome

Wait a minute... What is the name of the column the formula is in?

Kelly Buse

Hi Paul,

Sorry for not getting back to you sooner! We decided to minimize the RGB status balls to two - which made the formula much simpler!

Thanks again for all of your help!!

Paul Newcome

Happy to help! 👍️