Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formula to update status on a parent row based on any child row
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
Comments
-
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
-
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
-
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")))
