Formula to update status on a parent row based on child row with OR statement

christy.thompson106676

Hi - I need a formula to update the status on the parent row based on status values in the child rows below.  I am wresting with this formula and hoping someone on here can point me in the right direction.  I found this post which is exactly what I'm looking for but there was never an answer to her last comment about needing to add the logic if "all children are any of those 3 status's then Completed." which is the part I'm struggling with.

https://community.smartsheet.com/discussion/formula-update-status-parent-row-based-any-child-row

The four statuses are:

- Not Started

- In Progress

- Completed

- Cancelled

So here's the logic for populating the parent's status (I think I got it all, let me know if I missed anything!):

- If all children are Not Started, the parent is Not Started

- If all children are In Progress, the parent is In Progress

- If all children are Completed, the parent is Completed

- If all children are Cancelled, the parent is Cancelled

- If all children are Completed OR Cancelled, the parent is Completed

- If one child is In Progress, the parent is In Progress

 

Many thanks!

 

Frank Falco

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

IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress",

IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed",

IF(COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled",

IF(COUNTIF(CHILDREN(), "Completed")+COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Complete",

IF(COUNTIF(CHILDREN(), "In Progress")>0,"In Progress","---")))))

The "---" is for when none of the conditions are true, such as one is Not Started and others are Completed. You could add another condition for that case or others.

Paul Newcome

You can actually remove the second set of if all of the children are "In Progress" because regardless of whether it is all or just one, you have the "At least on is in progress" criteria established further down your list. You can also do the same with "All children are completed". Since you have that number factored into the completed/cancelled set, it is redundant. You can also cut out one of the other sets by accounting for the rest of your criteria and making the last possibility the "else" value for if none of the others are true.

 

One scenario I don't see factored in though is if you have some completed/cancelled and some not started. Your formula would return a blank for this because there are no tasks that are "In Progress" even though the parent task is.

 

Here is another option you could try if you like.

What it says:

1. If all children are "Not Started" then "Not Started".

2. IF all children are "Cancelled" then "Cancelled".

3. If all children are either "Cancelled" or "Completed" then "Completed". (This will not trigger if all are only "Cancelled" because we specified that scenario before this one. The IF statements will stop running at the first true value).

4. If there is any other combination not already specified then "In Progress" to include but not limited to

4a. At least one "In Progress"

4b. Combination of "Not Started" and "Completed" and/or "Cancelled"

.

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Cancelled")) = COUNT(CHILDREN()), "Completed", "In Progress")))

christy.thompson106676

Amazing - thank you so much!  I never would have come up with that.  And thank you for thinking of that extra scenario of if the children are a combination of Not Started, Completed or Cancelled.  Appreciate the help!

Paul Newcome

Happy to help! 

 

Sometimes there are numerous solutions to the same issue. My personal approach is to make it as short as possible. Other people may find it easier to be as specific as possible. What matters is what works best for you.

stephanie.blake67241

Can you please help me make a tweak to this formula? If any of the children are "Canceled" then the parent should be "Canceled.

Paul Newcome

Sure thing!

 

=IF(CONTAINS("Canceled", CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", "In Progress")))

stephanie.blake67241

Thanks but that didn't quite work. In the formula you provided, if all children are "Complete" the parent row shows "In Progress"...

Paul Newcome

Can you provide a screenshot? That doesn't sound right. That particular portion first counts all of the children that are "Completed". Then it counts all of the children. If both of those numbers match, then it will populate "Completed". Do you have any blank rows in your Children?

 

Are you using Completed or Complete? The text must be an exact match for it to work. I noticed you used Complete in your comment but the formula uses Completed.

stephanie.blake67241

That works, thanks!

Paul Newcome

Happy to help! 

m_dorsey

Would you be willing to help me tweak your original formula to also include a scenario of making the parent "On Deck" if there is a combination of only "Not Started" and "On Deck" children below?

I'm specifically referencing this formula:

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Cancelled")) = COUNT(CHILDREN()), "Completed", "In Progress")))

Lauren Darvesh

Hi Everyone

I'm also having terrible problems trying to emulate this same formula, I have read, and re-read this thread and tried so many times but I keep getting #UNPARSEABLE error. I simply cannot fathom for the life of me what the issue is. I'm not the greatest at formulas at the best of times but the advice from @Frank Falco seemed the easiest for me to copy, but still no success. Obviously I was replacing some of the status names in " " for my own ;) I didn't just copy and paste

I need to do pretty much the same thing as @christy.thompson106676 .

I've spent a good 2 hours trying to fix this but cannot. I would be grateful if someone could please give me the EXACT formula (I don't understand @row for example), of what formula I need to put in my parent rows to return the status result. I've actually given up.

So now what I need help with please is the below please:

If ANY ONE of the children is 'In Progress', then the parent is In Progress

If In Progress does not appear in any of the children rows, then just leave the parent status cell blank.

This will serve my purpose for now as I've spent far too long trying to fix this and I have to deliver a working dashboard by tomorrow morning which pulls in all my In Progress Parent rows from 9 different sheets.

Many thanks in advance!

Andrée Starå

Hi @Lauren Darvesh,

Can you paste the formula you’re trying?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Eric Leon

Could you please help me @Frank Falco.

All balls will start off Red, and team will manually change the Children to Green balls once task is complete. The hopeful outcome once all Children balls are complete, the Parent ball will automatically change to Green. Is there a formula you can do for the balls?

JennV

https://community.smartsheet.com/discussion/comment/216756#Comment_216756

@Paul Newcome Hi, Sorry to tag on to this discussion. After reading probably 50 of these posts, this is the formula that is the closest that works the best for me but I'm having 2 problems.

1. When all children are "Completed" then "In Progress" - supposed to be "Completed"
2. If all children are either "Cancelled" or "Completed" then "In Progress" - supposed to be "Completed."

I added a 4th Status "On Hold" and it's working correctly: all children "On Hold" then "On Hold" and any other combination the parent would be "In Progress." Here's the formula:

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), OR(3 = "Complete", 3 = "Cancelled")) = COUNT(CHILDREN()), "Complete", "In Progress")))

Thanks for the help!

Jenn