return the content of a column and its corresponding start date

System

This discussion was created from comments split from: Formulas to identify current project phase, next task due on a project plan and the task due date..

RKemp

Hi @Genevieve P. ,

I am hoping you can help me with a similar issue. I have a sheet of data which has parent and child rows. There are multiple activities either completed, in progress or not started.

What I would like to do, either in the Summary Sheet or the main sheet, is to return the content of a column and its corresponding start date, given that there are other sections above the data with activities in various stages of completeness.

For example, in this snap-shot, I would like to create a field that would display "Activity 2" and another field that would display "07/09/22" based on the fact that this is in phase 1 of the Monitoring section and is the next activity that is due to be started (i.e. "Activity 1" is set to complete).

How would I do this?

Thanks in advance for any support you can provide!

Kelly Moore

Hey @RKemp

Try this. Please verify my column names and edit the formula to replace the names with your column names. I took a guess at [Task Name], Status and [Start Date]

The first IF in the formula is checking if the row is a sub-parent, and has a status of 'In Progress'. Depending on how your 'In Progress' is determined you might not need the second IF, which looks to make sure there are still come Children rows incomplete. Assuming the first two IFs statements are true, the INDEX Collect should pull the next incomplete child row.

Your 'content' helper column

=IF(AND(COUNT(ANCESTORS([Task Name]@row)) > 0, COUNT(CHILDREN([Task Name]@row)) > 0, Status@row = "In Progress"), IF(COUNTIFS(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started")) > 0, INDEX(COLLECT(CHILDREN([Task Name]@row), CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started"), CHILDREN([Start Date]@row), AND(ISDATE(@cell), @cell = MIN(@cell))), 1)))

Your 'next date' helper column (don't forget to make this a Date column)

=IF(AND(COUNT(ANCESTORS([Task Name]@row)) > 0, COUNT(CHILDREN([Task Name]@row)) > 0, Status@row = "In Progress"), IF(COUNTIFS(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started")) > 0, INDEX(COLLECT(CHILDREN([Start Date]@row), CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started"), CHILDREN([Start Date]@row), AND(ISDATE(@cell), @cell = MIN(@cell))), 1)))

Would this work for you?

Kelly