Status update from daughter rows

Hi Smartsheets Team,

I have a column "Status" that has mother and daughter rows.

I am wanting the mother "Status" to update when the daughter rows change.

Screen Shot 2023-07-19 at 8.35.08 AM.png

So for example APQP would be "Not Started" until Pre APQP or MFG Plan Review is marked as "In Progress" then APQP becomes "In Progress". When BOTH are marked complete the APQP lines becomes "Complete".

Can you help me please?

Find more posts tagged with

Formulas

Accepted answers

Carson Penticuff

You will have to modify the name for [Primary Column] in the formula to match your sheet, but this should work.

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN([Primary Column]@row)), "Complete", "Not Started"))

All comments

Carson Penticuff

You will have to modify the name for [Primary Column] in the formula to match your sheet, but this should work.

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN([Primary Column]@row)), "Complete", "Not Started"))

Rod Sanderson

Wow thank you for your quick response - it works perfectly!! THANK YOU!

Christopher Warren

@Carson Penticuff

Could you show a modified the formula, with the same idea as original poster but where children drop drop option are:

Complete or N/A or Canceled = Complete

Progressing or Pending = Processing

Not Started = Not Started

Carson Penticuff

@Christopher Warren

I'm not sure I understand that logic entirely. What about situations where children have a combination of Complete and Not Started, or Complete and Progressing, or a combination of all three?

Christopher Warren

@Carson Penticuff

Children only one drop down value at a time.

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

Carson Penticuff

Give this one a try:

=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(AND(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "N/A") + COUNTIF(CHILDREN(), "Canceled") > 0, COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "N/A") + COUNTIF(CHILDREN(), "Canceled") < COUNT(CHILDREN([Primary Column]@row))), "Processing", IF(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "N/A") + COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN([Primary Column]@row)), "Completed", "Not Started"))))

Your column was calling anything containing completed as processing, regardless of other selections. You also mentioned "progressing" in your first post, but use "processing" in the formula in your last past. I used "processing" in this formula.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.