Automation of Status Drop Down at the Parent Level

Krista Cornew

Hi,

I want to automate the status column of the parent row based on the child rows.

For example, if all of the child rows are left blank or marked "Not Started," I want the parent row to read, "Not Started"

Or if a majority of the child rows are marked "In Progress" and some are marked "Complete" I'd like the parent row to read, "In Progress."

And finally, if all the child row tasks are marked complete the parent row automatically marks complete.

I can not figure out how this is done. Hopefully, this is possible and someone can help.

Genevieve P.

Hi Krista,

You could automate this with a Nested IF statement, telling the Parent row what to change to based on what's in the Child rows below.

I've built something similar with three IF statements. I'll break them out by instruction below, then show the full formula.

1 . "In Progress"

For my rule, I wanted the Parent row to show "In Progress" if any of the child rows have "In Progress"... even if all the child rows are Complete except one, then it's still not completed yet. This is why I've put the rule first in my statement, and I've used the CONTAINS function to find the words "In Progress" within the child rows:

IF(CONTAINS("In Progress", CHILDREN()),

"In Progress"

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress",

2 . "Complete"

Now, if none of the child rows say "In Progress", let's look to see if perhaps they're all Complete! We can do this by saying that If the number of Child Rows is the same as the number of Completed Child Rows, then the Parent should be complete.

However: since you noted that the rows could potentially be blank, we want to make sure that it only says Complete if there are no blank rows, as well. We can do this by adding an AND statement, saying that the Count of Children rows that are blank has to be 0. I've listed the two AND statements below:

IF(AND(

COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()),

COUNTIF(CHILDREN(), "") = 0),

"Complete"

IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Complete", IF

3 . "Not Started"

This one is a bit trickier. I have an OR statement for this... if the count of child rows that say "Not Started" is the same as the total count of child rows, then say "not started'. But we also want to check and make sure that if there's a mix of some rows saying "Not Started" AND some that are Blank, this should still say "Not Started'. Therefore, I've used the OR Function to say these two things:

IF(OR

(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()),

COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())),

"Not Started"

IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", 

This will then cover if ALL the children are blank, as well. HOWEVER! Keep in mind that this means if you have 4 child rows, and 2 are "Complete" but 2 are blank, it will actually read this as "Not Started" since the Count of Child rows (2 with text) is the same as the count of Blank Rows.

4 . Last Rule: OTHERWISE

As a final rule, we'll put "In Progress" as the default statement at the end. This will cover us for when there's a mixture of a few rows that are Complete and a few rows that are Not Started - it will return "In Progress"

"In Progress")))

5 . ***FULL FORMULA***

Put them all together and try this!

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Complete", IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", "In Progress")))

6 . Help Center Articles:

These are articles I used to build this.

IF Statements / CONTAINS Function / CHILDREN Function / COUNTIF Function / AND Function / OR Function

Let me know if this works for you, or if you have any questions!

Cheers,

Genevieve

Genevieve P.

Hi Krista,

You could automate this with a Nested IF statement, telling the Parent row what to change to based on what's in the Child rows below.

I've built something similar with three IF statements. I'll break them out by instruction below, then show the full formula.

1 . "In Progress"

For my rule, I wanted the Parent row to show "In Progress" if any of the child rows have "In Progress"... even if all the child rows are Complete except one, then it's still not completed yet. This is why I've put the rule first in my statement, and I've used the CONTAINS function to find the words "In Progress" within the child rows:

IF(CONTAINS("In Progress", CHILDREN()),

"In Progress"

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress",

2 . "Complete"

Now, if none of the child rows say "In Progress", let's look to see if perhaps they're all Complete! We can do this by saying that If the number of Child Rows is the same as the number of Completed Child Rows, then the Parent should be complete.

However: since you noted that the rows could potentially be blank, we want to make sure that it only says Complete if there are no blank rows, as well. We can do this by adding an AND statement, saying that the Count of Children rows that are blank has to be 0. I've listed the two AND statements below:

IF(AND(

COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()),

COUNTIF(CHILDREN(), "") = 0),

"Complete"

IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Complete", IF

3 . "Not Started"

This one is a bit trickier. I have an OR statement for this... if the count of child rows that say "Not Started" is the same as the total count of child rows, then say "not started'. But we also want to check and make sure that if there's a mix of some rows saying "Not Started" AND some that are Blank, this should still say "Not Started'. Therefore, I've used the OR Function to say these two things:

IF(OR

(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()),

COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())),

"Not Started"

IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", 

This will then cover if ALL the children are blank, as well. HOWEVER! Keep in mind that this means if you have 4 child rows, and 2 are "Complete" but 2 are blank, it will actually read this as "Not Started" since the Count of Child rows (2 with text) is the same as the count of Blank Rows.

4 . Last Rule: OTHERWISE

As a final rule, we'll put "In Progress" as the default statement at the end. This will cover us for when there's a mixture of a few rows that are Complete and a few rows that are Not Started - it will return "In Progress"

"In Progress")))

5 . ***FULL FORMULA***

Put them all together and try this!

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Complete", IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", "In Progress")))

6 . Help Center Articles:

These are articles I used to build this.

IF Statements / CONTAINS Function / CHILDREN Function / COUNTIF Function / AND Function / OR Function

Let me know if this works for you, or if you have any questions!

Cheers,

Genevieve

Krista Cornew

Wow thank you! This was super helpful. I would have never been able to write this myself!

Genevieve P.

I'm so glad it worked for you! Let me know if you need to add any more criteria and I'm happy to help. 🙂

Chris Jernigan

This is super helpful. Is there anyway to add another State of Impeded?

If any child row is Impeded then the parent row should show Impeded.

I try to modify the formula but it only laughs at me 😁

Genevieve P.

Hi @Chris Jernigan

Yes, no problem! You would want to add that statement at the very beginning, the first rule for the formula to look at:

=IF(CONTAINS("Impeded", CHILDREN()), "Impeded"

Add that to the start, and add an extra closing parenthesis at the very end of the entire formula to close it off. Try this:

=IF(CONTAINS("Impeded", CHILDREN()), "Impeded", IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Complete", IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", "In Progress"))))

If that doesn't work, let me know all instances of when you want the parent row to show "Impeded" and we can add in other criteria.

Cheers!

Genevieve

Chris Jernigan

That is perfect!

Thank you for the quick response.

Genevieve P.

Hi @Chris Jernigan

No problem at all!

Jorden Thomas

Could I use the formulas above (with alterations) to change a dropdown status of a child row based on the parent? Example: Parent Row = Priority Contracts for Review; when I move a Child row under that Parent row, I would like the Child's status to be updated to match the Parent status (Priority Contracts for Review).

The status updates in the card view, but I would like the status to automatically update when I move the row in grid view.

Genevieve P.

Hi @Jorden Thomas

If you're using that dropdown column for your lanes in card view, adding a formula to the column means that you won't be able to manually move cards (or if you do, it will erase the formula).

Could you perhaps post two screen captures with your current set-up in grid view and card view?

A simple option would be to add a helper column in your sheet and use the following formula:

=PARENT(Status@row)

This will bring in the Parent data from a column called "Status" to this child row, but in a different column.

Cheers,

Genevieve