return the content of a column and its corresponding start date

Options

Answers

  • RKemp
    Options

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

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!