Parent status based on Child status

Options

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.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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"))))))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.
    Sam V. ✭✭
    edited 12/09/22
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options
  • liz_alvarez
    liz_alvarez ✭✭✭
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • liz_alvarez
    liz_alvarez ✭✭✭
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    liz_alvarez ✭✭✭
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    liz_alvarez ✭✭✭
    Options

    That is exactly right! How odd!