#### 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

Options
edited 12/09/19

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

Tags:

• Employee
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

This discussion has been closed.