Automating RYG balls to change colors

So i have parent cells and dependencies. The dependencies have color balls (red, yellow, green, grey). Here is what I want:

If one of the dependencies has a red color ball, I want the parent ball color to be red.

If all dependencies have a green ball, I want the ball for the parent to be green

If all dependencies have a yellow ball, I want the ball for the parent to be yellow

If all dependencies have a grey ball, I want the ball for the parent to be grey

Red ball in the midst of other color balls take precedence over all the other colors and the parent shows up as red. Next is yellow, grey then green.

Can you help with that?

Find more posts tagged with

Non-profit

Sheets

Healthcare

Education

Formulas

Community Platform

Product Development

Conditional Formatting

Gantt View

Comments

L_123

=if(countif(D1:D6,"Red")>0,"Red",if(Count(D1:D6)=countif(D1:D6,"Yellow"),"Yellow",if(Count(D1:D6)=countif(D1:D6,"Grey"),"Grey",if(Count(D1:D6)=countif(D1:D6,"Green"),"Green"

This is exactly as you phrased it above, but that said there are a lot of gaps in this formula. Examples of gaps would be if one is grey and the rest green, it will be blank. I believe you mean any instead of all, in which case the formula below is what you want.

=if(countif(D1:D6,"Red")>0,"Red",if(countif(D1:D6,"Yellow")>0,"Yellow",if(countif(D1:D6,"Grey")>0,"Grey",if(countif(D1:D6,"Green")>0,"Green"

Sarah Thompson

I would tweak this slightly to use the children feature instead of D1:D6 in the event that you frequently add child rows. Otherwise if you're risking not counting all children.

so =IF(countif(children() "Red")>0, "Red", etc.

NaNa

Thank you!!! The second formula worked! It was exactly what I needed!

Original

=if(countif(D1:D6,"Red")>0,"Red",if(countif(D1:D6,"Yellow")>0,"Yellow",if(countif(D1:D6,"Grey")>0,"Grey",if(countif(D1:D6,"Green")>0,"Green"

Changed it to fit my cells

=IF(COUNTIF([Task Status]9:[Task Status]18, "Red") > 0, "Red", IF(COUNTIF([Task Status]9:[Task Status]18, "Yellow") > 0, "Yellow", IF(COUNTIF([Task Status]9:[Task Status]18, "Grey") > 0, "Grey", IF(COUNTIF([Task Status]9:[Task Status]18, "Green") > 0, "Green"))))

Paul Newcome

You can also shorten it and account for any parent row without having to update specific row numbers.

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Grey", CHILDREN()), "Grey", "Green")))

This can be used in any parent row with any number of children without any modification at all.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…