Need help showing child row statuses in parent row status

Options

Hi! I need help creating a formula that shows the child row statuses in the parent row. Is the below possible to build into a formula that I can pop into the parent row?

  • Show “Not Started” if all child rows say “Not Started”
  • Show “On Track” if any of the child rows say “On Track”
  • Show “With Client” if any of the child rows say “With Client”
  • Show “At Risk” if any of the child rows say “At Risk”
  • Show as “Complete” if all child rows are “Complete”
  • Show as “Cancelled” if all child rows are “Cancelled”

Thanks!

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Karey

    Here is the formula requested. If there is a formula that is inserting the Child row response, let me know and we can combine the two formulas into one column formula. If you are manually choosing the response in the Child row then you must manually insert this every time into the Parent row. If you inserted a helper column, you could pull the existing Child data into the helper and then have a column formula that would automatically register the response. (If adding a helper then in all the CHILDREN() terms you would need to add CHILDREN(Status@row) ) to point to what column you are pulling Children responses from)

    There are some missing conditions which will result in the Parent status being blank. For example, if you have a mix of "Not Started" and "Complete".

    =IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "At Risk") > 0, "At Risk", IF(COUNTIFS(CHILDREN(), "On Track") > 0, "On Track", IF(COUNTIFS(CHILDREN(), "With Client") > 0, "With Client", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNTIFS(CHILDREN(), <>""), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNTIFS(CHILDREN(), <>""), "Complete", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNTIFS(CHILDREN(), <>""), "Cancelled")))))))

    Will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Karey

    Here is the formula requested. If there is a formula that is inserting the Child row response, let me know and we can combine the two formulas into one column formula. If you are manually choosing the response in the Child row then you must manually insert this every time into the Parent row. If you inserted a helper column, you could pull the existing Child data into the helper and then have a column formula that would automatically register the response. (If adding a helper then in all the CHILDREN() terms you would need to add CHILDREN(Status@row) ) to point to what column you are pulling Children responses from)

    There are some missing conditions which will result in the Parent status being blank. For example, if you have a mix of "Not Started" and "Complete".

    =IF(COUNT(CHILDREN()) > 0, IF(COUNTIFS(CHILDREN(), "At Risk") > 0, "At Risk", IF(COUNTIFS(CHILDREN(), "On Track") > 0, "On Track", IF(COUNTIFS(CHILDREN(), "With Client") > 0, "With Client", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNTIFS(CHILDREN(), <>""), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNTIFS(CHILDREN(), <>""), "Complete", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNTIFS(CHILDREN(), <>""), "Cancelled")))))))

    Will this work for you?

    Kelly

  • Karey
    Karey ✭✭
    Options

    Thank you for your help Kelly! This is precisely what I needed!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!