Formula to check a parent checkbox when any child is complete & formula to complete a parent status

Anthony DAmbrosio

Hi Smartsheets Community,

I need some help with a formula that I am stuck on. Below is a screen shot of grandparent to parent, to child hierarchy. Grandparent is in yellow, parent is in blue, and child is not highlighted. I put

I am using the following formula to add a check mark on the parent row when all of the children under each parent are all check and when all of the parent rows are checked, then it adds a check mark to the grandparent row. This formula appears to be working.

=IF((COUNT(CHILDREN([Dealer Name]@row))) > 0, IF(COUNT(CHILDREN([Dealer Name]@row)) = COUNTIFS(CHILDREN(), 1), 1))

Next, I am using the formula below to change the Stage Status as follows, but something is not working correctly

1. When any of the children under a parent row are checked the stage status changes to "Complete"
2. When any of the children are check, I want the parent to show "In Progress"
3. When any of the parent rows are showing "In Progress", I want the grandparent row to show "In Progress"
4. When all of the children under a parent are checked off, I want the parent row to show "Complete"
5. When all of the parent rows are check and the stage status for all parents shows "Complete" I want the grandparent to change to "Complete"

What am I doing wrong? Any help would be greatly appreciated. Formula to check a parent checkbox when any child is complete & formula to complete a parent status

=IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

KPH

Now I am wondering if I misunderstood which stage status you mean (I thought parent, but now I think you mean child)! I have rearranged the criteria, reworded a few, and added a couple. Is this correct?

1. If a child Task Complete is checked, the Child Stage Status changes to "Complete".
2. If a child Task Complete is unchecked the Child Stage Status is "Not Started".
3. If any Child Stage Status is "Not Started" the Parent Stage Status shows "Not Started"
4. When any but not all of the children have Task Complete checked, the Parent Stage Status shows "In Progress"
5. When all of the children under a parent are checked off, the Parent Stage Status shows "Complete"
6. When any of the Parent Stage Status show "In Progress", the grandparent Stage Status shows "In Progress"
7. When all of the parent rows are check and the stage status for all parents shows "Complete" the grandparent shows "Complete"

With the exception of the part in bold, @Nick055 's second formula does exactly what you need.

=IF(COUNT(CHILDREN()) > 0, IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Started"), "Not Started", "In Progress")), IF([Task Complete]@row = 1, "Complete", "Not Started"))

The only thing it does not do is check whether any of the parent Task Complete boxes are checked (requirement 7). Does it need to? Nick's formula will change the Grandparent Stage Status to "Complete" if all the Parent Stage Status are "Complete". It does not look at the check boxes. If it also needs to check the parent check boxes, let us know and we can adjust the formula.

KPH

Can you clarify your requirements? Number 1 is:

1. When any of the children under a parent row are checked the stage status changes to "Complete"

Do you mean are checked or do you mean have a Stage Status Complete?

Do you mean any or all? Any is going to be difficult as a parent could have a child with each, what should be returned in that case?

Nick055

Hi Anthony,

I am not sure if this is exactly what you are asking for but it might help.

I do have two solutions for the "Stage Status" formula

-----------------------

This solution is if you want to be able to change a child tasks status manually.

Copy this formula into the parents / grandparents of "Stage Status"

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Started"), "Not Started", "In Progress"))

----------------------------

This solution makes most of this automated assuming the child tasks are never "In Progress",

You can set this as the column formula for the "Stage Status" column

=IF(COUNT(CHILDREN()) > 0, IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Started"), "Not Started", "In Progress")), IF([Task Complete]@row = 1, "Complete", "Not Started"))

This makes it so when the child tasks are unchecked they are "Not Started" and will change to "Complete" when checked. Parents will then see if all of their child tasks are complete & if so will be "Complete". If not, it'll check if their tasks are all "Not Started", if so will be "Not Started". If any of the children are complete but not all, it'll be marked "In Progress. This same rule will apply to the grand parents & their children the same way.Also, for the "Task Complete" column.

If you do this, you could continue to use the "Task Complete" formula in Parents / Grandparents.

There is another option for the "Task Complete" column, but it has a minor flaw. You could create a helper column that fully checks parents & grandparents boxes based on the "Stage Status" column. BUT, they would appear empty in the "Task Complete" column where you check the children off ):

Create a helper column (like "Task Complete Helper") and set the column formula to this

=IF([Stage Status]@row = "Complete", 1, 0)

Anthony DAmbrosio

@KPH,

Thank you for getting back to me so quickly. Below are my answers to my requirement # 1. It's supposed to say when ALL children are are checked under a parent row the stage status changes to "Complete"

My apologies as I typed it incorrectly.

Best,

Anthony

KPH

Now I am wondering if I misunderstood which stage status you mean (I thought parent, but now I think you mean child)! I have rearranged the criteria, reworded a few, and added a couple. Is this correct?

1. If a child Task Complete is checked, the Child Stage Status changes to "Complete".
2. If a child Task Complete is unchecked the Child Stage Status is "Not Started".
3. If any Child Stage Status is "Not Started" the Parent Stage Status shows "Not Started"
4. When any but not all of the children have Task Complete checked, the Parent Stage Status shows "In Progress"
5. When all of the children under a parent are checked off, the Parent Stage Status shows "Complete"
6. When any of the Parent Stage Status show "In Progress", the grandparent Stage Status shows "In Progress"
7. When all of the parent rows are check and the stage status for all parents shows "Complete" the grandparent shows "Complete"

With the exception of the part in bold, @Nick055 's second formula does exactly what you need.

=IF(COUNT(CHILDREN()) > 0, IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Started"), "Not Started", "In Progress")), IF([Task Complete]@row = 1, "Complete", "Not Started"))

The only thing it does not do is check whether any of the parent Task Complete boxes are checked (requirement 7). Does it need to? Nick's formula will change the Grandparent Stage Status to "Complete" if all the Parent Stage Status are "Complete". It does not look at the check boxes. If it also needs to check the parent check boxes, let us know and we can adjust the formula.

Anthony DAmbrosio

@KPH and @Nick055

I appreciate both of you helping out with a solution and providing me with the formula. What you both provided helped and it worked.

Hope you both had a nice weekend and hope you have a great week.

All the best,

Anthony D'Ambrosio