Formula to update status on a parent row based on any child row

ChristineE

Hi,

Wondering if it is possible to create a formula to change the status of a parent row when a child status is updated.

 

We have a number of Parent Status's and Child Status's ie

If  All Children are Unallocated then Parent is Unallocated

If All Children are Allocated then Parent is Allocated

But if any Child is In Progress or QA then Parent is In Progress

then when all Children are any of Despatched, No Action Req or Cancelled, then Parent Status is Complete

Any thoughts of how to achieve this are appreciated.

 

Christine

Shaine Greenwood

Hi Christine,

Try something like this in your parent row:

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

The logic of the formulas is if the count of children is equal to the count of children that are "Unallocated", return "Unallocated" in the parent; same with "Allocated." Otherwise, if neither of the prior conditions are met, return "In Progress."

The count/countif functions will only count NON-BLANK cells, so if all or some of your children are blank, this may return an unexpected value.

Check out our list of functions for more on COUNT and COUNTIF: https://help.smartsheet.com/functions

ChristineE

Thanks Shaine

 

This works great but need to add in the bit about Completed.

Have the formula as this currently but it needs all children to be completed or cancelled or no action req to work. I need it to be if all children are any of those 3 status's then Completed.

=IF(COUNTIF(CHILDREN(), "Unallocated") = COUNT(CHILDREN()), "Unallocated", IF(COUNTIF(CHILDREN(), "Allocated") = COUNT(CHILDREN()), "Allocated",IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed",IF(COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Completed",IF(COUNTIF(CHILDREN(), "No Action Req") = COUNT(CHILDREN()), "Completed", "In Progress")

 

thanks

Paul Newcome

You could give something like this a try...

.

=IF(COUNTIFS(CHILDREN(), "Unallocated") = COUNT(CHILDREN()), "Unallocated", IF(COUNTIFS(CHILDREN(), "Allocated") = COUNT(CHILDREN()), "Allocated", IF(OR(CONTAINS("In Progress", CHILDREN()), CONTAINS("QA", CHILDREN())), "In Progress", "Completed")))