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

ChristineE
edited 12/09/19 in Archived 2017 Posts

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:

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

  • Paul Newcome
    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")))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

This discussion has been closed.