Parent RYG(B) status balls automated based on Child status

Gunn_Jack

Hello,

I have been scanning the community and have found some bits for what I am trying to do but am struggling to put it altogether and was wondering if anyone could help?

I have a list of tasks with headers (parents) which then have sub tasks (children) and some of them also have sub tasks (children again i believe). The first thing I wanted to do was for the Parent status to update based on the Child statues. I did so with this formula:

=IF(COUNTIF(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Yellow"))

What i want to do is take it a step further and say, if all Children are Blue (complete) then change the parent status to Blue also, but if not, follow the above formula.

I tried nesting this into the start of the above formula, but im just getting INVALID:

=COUNTIF([Status]:[Status],"Blue")

Any ideas much appreciated?

Thanks,

Jack

Kara Lumley

Hey Jack, 

Here's a couple examples that might get you started:

if most of the children are red, make the parent red, if most of the children are green, make the parent green, otherwise, make the parent yellow, if most of the children are blue, make the parent blue.

=IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Blue")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Blue")), "Red", IF(AND(COUNTIF(CHILDREN(), "Blue") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Blue") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Blue") > COUNTIF(CHILDREN(), "Red")), "Blue", "Yellow")))

Counts how many of the child rows are green and if it is equal to the number of children it marks it as Green, if not it and there is at least 1 “Red”, it marks it as Red otherwise it is marked as “Yellow”.

=IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow"))

Best,

Kara

Gunn_Jack

Thank you Kara.

I have slept since the above post and since realised I was going after the wrong thing. Like you say, the 1st formula counts what has the majority, and therefore when over half of the tasks are completed, it turns the parent blue, which is incorrect.

What I was thinking I needed was the second bit you posted and I had just came here to look for it so thank you for making it so easy for me! 

I have thought about taking it a bit further:

=IF([Task Complete]1= 1, "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "Green"))

So this says, if 'Task Complete 1' (my Parent completed checkbox) is ticked, then complete the Parent section, if not, if all Children are Green, then Green but if there is one Red, then colour the Parent Red, if not, if there is one Yellow then colour the Parent Yellow, and finally if none of that is satisfied (which it should be), default to Green. 

Thanks again Kara, your suggestion meant I had the bones to build the above, much appreciated.

Jack.