Show Active Child Status in Parent Row

Parent has 26 Children (26 week program). Only one Child row will be active per Parent row. Each Child will have a different task in the 'Status' column(single dropdown list). You will select Complete to close the current Child and activate the next Child selecting a different task in dropdown list. I want to show the current status of the child that is active in the parent row.

Sheet example: Current active Child row status is LOA which should show in Parent cell


Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), <> "Complete"), 1)

    In English, get the Status value from the first Status cell in this row's child rows that does not equal Complete.

    Once you are sure that it works, wrap it in IFERROR, because once all child rows are Complete, the formula will error with #INVALID VALUE. You can set your IFERROR to leave the cell blank, or to display a message like "All Weeks Complete".

    Blank when all child rows complete:

    =IFERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), <> "Complete"), 1), "")

    With message when all rows complete:

    =IFERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), <> "Complete"), 1), "All Weeks Complete")


    Help pages for INDEX, COLLECT, IFERROR

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), <> "Complete"), 1)

    In English, get the Status value from the first Status cell in this row's child rows that does not equal Complete.

    Once you are sure that it works, wrap it in IFERROR, because once all child rows are Complete, the formula will error with #INVALID VALUE. You can set your IFERROR to leave the cell blank, or to display a message like "All Weeks Complete".

    Blank when all child rows complete:

    =IFERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), <> "Complete"), 1), "")

    With message when all rows complete:

    =IFERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), <> "Complete"), 1), "All Weeks Complete")


    Help pages for INDEX, COLLECT, IFERROR

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Marc1a
    Marc1a ✭✭✭

    That Worked. Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!