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

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

Find more posts tagged with

Formulas

Conditional Formatting

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…