Show Active Child Status in Parent Row

Marc1a

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

Jeff Reisman

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

Jeff Reisman

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

Marc1a

That Worked. Thank you