Parent status based on Child status

Sam V.

Hi-

I am currently working on something for a group that does not want to automate their child task statuses. I have scoured the answers here to try and find anything that I can customize to work with their parent rows for an automated update, but I keep coming up a little short. I'm not sure if it's maybe too many combinations, or if I'm just not writing it out correctly so any help would be appreciated!

They have the following statuses on their sheet-

·        In Progress

·        On Deck

·        On Hold

·        Canceled

·        Complete

What I'm trying to show is -

·        If any children tasks are In Progress the parent=In Progress

·        If all children tasks are On Deck the parent=On Deck

·        If all children tasks are On Hold the parent = On Hold

·        If all children tasks are Canceled the parent=Canceled

·        If all children tasks are Complete the parent=Complete

·        If there is a combination of On Deck, On Hold, Canceled, and Complete parent=In Progress

·        If there is a combination of On Hold and Canceled OR On Hold & Complete, parent=On Hold

·        If there is a combination of Canceled and Complete parent=Complete

·        If the children rows are blank parent=blank or On Deck if blank is not possible

 

The other part I’m having an issue with is they wanted an auto-flag for At Risk, I’ve created that, but I’m not sure how to tie it into the status updates to show the parent as At Risk if the flag is checked.

Paul Newcome

Give this a try...

=IF(COUNTIFS(CHILDREN(), @cell <> "") = 0, "", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "On Deck") = COUNT(CHILDREN()), "On Deck", IF(COUNTIFS(CHILDREN(), @cell = "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Canceled", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold")> 0, "On Hold", "In Progress"))))))

Paul Newcome

Give this a try...

=IF(COUNTIFS(CHILDREN(), @cell <> "") = 0, "", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "On Deck") = COUNT(CHILDREN()), "On Deck", IF(COUNTIFS(CHILDREN(), @cell = "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Canceled", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold")> 0, "On Hold", "In Progress"))))))

Sam V.

Almost, yes, thank you so much!

It's that On Hold part that is making it not work quite as it should. It should only reflect as On Hold if all of the children are On Hold. Is there any way to get it to show In Progress if there is a combination of statuses like this:

EDIT:

Actually. I just answered my own question by changing the ending of the statement. Thank you again for the support, I appreciate it!

=IF(COUNTIFS(CHILDREN(), @cell <> "") = 0, "", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), @cell = "On Deck") = COUNT(CHILDREN()), "On Deck", IF(COUNTIFS(CHILDREN(), @cell = "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Canceled", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "On Hold") = COUNT(CHILDREN()), "On Hold", "In Progress"))))))

Paul Newcome

Glad you got it sorted. 👍️

liz_alvarez

@Paul Newcome - I am hoping you can help me with something similar: I need this formula to return Complete if the other Child statuses are some thing else:

=IF(COUNTIFS(CHILDREN(), @cell <> "") = 0, "", IF(COUNTIFS(CHILDREN(), @cell = "Getting Requirements") = COUNT(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Requirements Complete") = COUNT(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Development In Process") = COUNT(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Ready for Review") = COUNT(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), @cell = "Feedback sent to MHI") = COUNT(CHILDREN()), "In Progress"), "Complete"), "Complete"), "Complete"), "Complete"), "Complete")

I know this is incorrect, I am just not sure where to put the "Complete"

Paul Newcome

@liz_alvarez Are you able to explain everything you are wanting to accomplish with this formula?

liz_alvarez

@Paul Newcome - yes!

I have these statuses:

• Getting Requirements
• Requirements Complete
• Development In Process
• Ready for Review
• Feedback sent to MHI
• Complete/Signed Off
• No Longer Needed

For all of these - Getting Requirements, Requirements Complete, Development In Process, Ready for Review, Feedback sent to MHI - I want the parent task to be "In Progress"

For "Complete/Signed Off", I want the parent task to be "Complete"

No Longer Needed - can be excluded

Thanks for taking a look!

Liz

Paul Newcome

@liz_alvarez Try this:

=IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete/Signed Off", @cell = "No Longer Needed")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", "In Progress")

liz_alvarez

@Paul Nelson - That is going to work; how would it need to be written so that if the status of the children are all blank, that the parent is blank?

Thanks so much for all of the help, I really appreciate it!

Liz

Paul Newcome

@liz_alvarez It would look like this:

=IF(COUNTIFS(CHILDREN(), @cell <> "") <> 0, IF(COUNTIFS(CHILDREN(), OR(@cell = "Complete/Signed Off", @cell = "No Longer Needed")) = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", "In Progress")

liz_alvarez

That is exactly right! How odd!