Update Parent Row based on Child status

Hi!

I want the Parent row to automatically change with the most recent Child Row status. I currently do this manually so I have to update the status in 2 different rows.

Right now I have this:

=IF(COUNTIFS(CHILDREN(), "Open") > 0, "Open", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIFS(CHILDREN(), "In Verification") > 0, "In Verification", IF(COUNTIFS(CHILDREN(), "Ready for Verification") > 0, " Ready for Verification", "Defined"))))

However, I also have Child rows in Accepted status, but Accepted is not always the last stage. It would be better if the Parent Row would just update from the most recent Child change, but I can't figure that out.

Parent Rows are in grey.

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Project Management

Accepted answers

All comments

Bassam Khalil

Hi @Molly Kuhn

Hope you are fine, you can use the following formula in each parent level after you add a system column "Modified (Date )"

=INDEX(Modified3:Status6, MATCH(MAX(CHILDREN(Modified@row)), Modified3:Modified6), 3)

the following screenshot shows the result

Troy Jones

Basaam,

In these sheets, there are large numbers of parent rows and new children rows are being added to parent rows daily. We need to find a formula which can be added to the status column of every parent row without having to specify row numbers. Is there a way to make this formula work for every parent row without having to specify row numbers in the range references?

Thank you.

Troy Jones

Molly Kuhn

@Bassam.M Khalil

Troy is working with me on updating our Smartsheets.

Maybe this screenshot can give you a better example of what we're dealing with.

Basically, when we create a new child row, I would like to just be able to update the necessary fields from a report and the parent row status will automatically update without having to open the sheet to manually change the parent row.

Kelly Moore

Hey @Molly Kuhn

As an alternative to @Bassam.M Khalil solution, I offer the formula below to try. I would also recommend inserting one DATE helper column and use the automation Record a Date to capture the date when changes are made specifically in your status column. Other inadvertent changes in the row would affect the Modified column. In my formula below, I called this Date helper column 'Record Date'. The column does need to be added and saved to the sheet prior to creating the automated workflow.

IF your Children cells are being updated by a formula (vs manual entry), we can easily build a formula that differentiates between what a parent row is supposed to calculate and the formula in a Child row. If this is something you need help with, reach back out to @Bassam.M Khalil or me. We can help you with that.

If you do not wish to use Record Date helper column, replace the date column with Bassam's suggestion of the Modified column. This formula would go in every parent row. The range is specified by calling out the Children@row - because it's a parent row the parent knows which rows are it's children.

=INDEX(COLLECT(CHILDREN(Status@row), CHILDREN([Record Date]@row), MAX(CHILDREN([Record Date]@row))), 1)

If you need help with the Record Date Automation, please see the screenshot below as a reference

Let us know if this works for you.

Kelly

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.