Another automate RYG balls for parent based on child

Bazza

Hi,

I have a project plan with multiple levels of children. Currently, the RYG automation is as follows:

 

=IF(Status@row = "Done", "Gray", IF([Due Date]@row > TODAY(+7), "Green", IF(AND([Due Date]@row >= TODAY(+3), [Due Date]@row < TODAY(+7)), "Yellow", IF([Due Date]@row < TODAY(+3), "Red"))))

 

What I want to do is create a formula that identifies children and applies the formula above to them. Then, for higher levels of parents, it should be red if there is a red, yellow is there is a yellow but not red and green if there is no red or yellow, so that the parents inherit the "worst" level of the children.

 

I'm not really sure where to start with this so any help is appreciated.

 

All the best

 

Barry

Paul Newcome

You already have your child level formula above. Now you just need your parent level formula.

 

Something like this should work...

 

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

.

Now we just combine the two. If a row has children...

 

=IF(COUNT(CHILDREN()) > 0, 

.

Then run the Parent formula...

 

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

.

Otherwise run the Child formula...

 

=IF(COUNT(CHILDREN()) > 0, IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", "Gray"))), IF(Status@row = "Done", "Gray", IF([Due Date]@row > TODAY(+7), "Green", IF(AND([Due Date]@row >= TODAY(+3), [Due Date]@row < TODAY(+7)), "Yellow", IF([Due Date]@row < TODAY(+3), "Red")))))

.

Give that a go and see how it works for you.

Cathy Verhulst

Hi Paul, I have a question related to this.

In my case it's not RYG balls, but "Yes", "Hold", "No" statuses.

I am using the following formula in the PARENT row:

=IF(CONTAINS("Yes"; CHILDREN()); "Yes")

But I have an extra condition. In the Column on the left of this Yes/Hold/No column is a Checkbox column (we use for requesting approval).

My goal is to make the PARENT row give a "Yes", when ALL the CHILDREN row that have a Checkbox checked, AND the Yes/Hold/No Column is a "Yes". What I want to avoid is that the PARENT row shows a "Yes", if only one of the rows is approved. I want to show a YES, when everything that requested approval (so has a checked checkbox), is also approved (with a "Yes).

Can you help me with this?

Paul Newcome

@Cathy Verhulst First we want to count how many children have a box checked.

=COUNTIFS(CHILDREN([Checkbox Column]@row), 1)

Now we want to count how many children are "Yes".

=COUNTIFS(CHILDREN(), "Yes")

Next we say that IF those two counts equal each other (meaning all that are checked are "Yes") then output "Yes".

=IF(checked_count = Yes_count, "Yes")

=IF(COUNTIFS(CHILDREN([Checkbox Column]@row), 1) = COUNTIFS(CHILDREN(), "Yes"), "Yes")

Cathy Verhulst

Ah yes! That's the logic I was looking for, thank you so much for your help Paul! 😁

Works like a charm

Paul Newcome

Happy to help. 👍️