Formula Automations

Krista Cornew

Hey everyone! I am trying to adjust or update the formula below to include "if the status column is changed to "terminated" the parent row will read that as "complete" and when all the other child rows are marked "complete" the parent row will change to complete. However, I also need the parent row to read "in progress' if the child rows are marked as a mix of "blank", "in progress" or "terminated."

Hoping this makes sense and/or someone can help me!

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "") = 0), "Complete", IF(OR(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "") = COUNT(CHILDREN())), "Not Started", "In Progress")))  

SoS | Dan Palenchar

Are you updating the Status cell of the parent row based on your children rows? I'm not completely clear on your set up, but I'm assuming you want to accomplish the following for your Parent rows:

1. If all Child rows are Complete OR all child rows are terminated make the Status "In Progress"
2. If the Child rows have any combination of various statuses, mark parent "In Progress".

This formula accomplishes the above:

=IF(OR(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNTIF(CHILDREN(), "Terminated") = COUNT(CHILDREN())), "Complete", "In Progress")

Krista Cornew

Hey Dan,

This is close but not quite right yet.

I need the formula to read complete if there is a mix of complete and terminated as well.

1. If all Child rows are Complete OR all child rows are terminated or a mix of terminated and complete make the Status "Complete"
2. If the Child rows have any combination of various statuses, mark parent "In Progress".

Thanks!