Status update from children rows

Christopher Warren

Hi Smartsheets Team,

I have a column "Task" that has parent and children rows.

I am wanting the parent row "Status" to update when the children row "Status" changes.

For example, Parent row (Real Estate) would be "Not Started" until one of the Children Rows (Pro Forma, LOI, PSA, etc) are marked as "Processing", "Pending", "Completed", "N/A" or "Canceled" then Parent row (Real Estate) would become "Processing". When all children are marked "Completed" or "N/A" or "Canceled" the Parent row is changed to "Completed".

I have this thus far, but can't figure out when all children are either Complete &/or N/A &/or Canceled but one is Not Started? I'd like for Parent status to show as Pending, but Parent shows Not Started.

=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started"))))

If I use the following formula, even when all are Complete, N/A or Canceled, Parent still says Processing:

=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started"))))))

I appreciate your time.

Paul Newcome

You would continue with the nested IF statement adding more IFs as needed until all are accounted for.

Christopher Warren

I can't figure out how to get the Parent to Completed if all children are Completed &/or N/A &/or Canceled & if they are not all Completed &/or N/A &/or Canceled, the parent would be either processing or not started

=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN(Status@row)), "Completed", "Not Started", IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN(Status@row)), "Completed", "Not Started", IF(COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN(Status@row)), "Completed", "Not Started")))))

Paul Newcome

Instead of

COUNTIF(CHILDREN(), "Completed")

you would use

COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled"))

Christopher Warren

Many thanks @Paul Newcome

Paul Newcome

Happy to help. 👍️

Christopher Warren

@Paul Newcome

How about if all are complete &/or N/A &/or Canceled but one is Not Started? The parent shows Not Started.

Paul Newcome

You would use the IF/CONTAINS combo similar to your "Processing" piece.

Christopher Warren

@Paul Newcome

This is the formula I'm using:

=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started"))))

When I add the following, the parent never changes to "Completed"

IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing"

Paul Newcome

It depends on where exactly you are adding it, but I venture to say you are adding it before the "Completed" COUNTIF. Nested IFs work from left to right and stop on the first true value. So if even one child cell contains "Completed", it is going to flag as true and stop evaluating there. You most likely need to rearrange the order of your IF statements.

Christopher Warren

@Paul Newcome

I hate to keep asking but, I can't get this formula to work

=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing"))))))

Paul Newcome

Try moving the "Not Started" piece to the end of the formula.

Christopher Warren

@Paul Newcome

If I use the following formula, even when all are Complete, N/A or Canceled, Parent still says Processing:

=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started"))))))

Paul Newcome

Right. That goes back to my previous comment:

"Nested IFs work from left to right and stop on the first true value. So if even one child cell contains "Completed", it is going to flag as true and stop evaluating there. You most likely need to rearrange the order of your IF statements."