Update Parent Row based on Child status

Options

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.


Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Troy Jones
    Options

    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
    Options

    @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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!