Formula Automations

Options

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

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

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


    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Krista Cornew
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!