Formula to identify the task name of next child task due

Hi! I've looked at past posts to try to figure this out, but nothing seems to work.

I'm trying to get a formula to work that will show me, in my parent task row, the Task Name of the child task that is due next, and then the due date of that task.

In my screenshot example, i would want the formula to return Task 5, and its due date of 1/8

I do have a helper row (checkbox column) that is checked if the row is a parent row, if that helps.

Thanks in advance for your help!!



Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/06/24

    Hey @LSxx24

    Try this. This can be a column formula if desired.

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 1, [Parent Helper]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN([Task Name]@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

    *rename the [Parent Helper] in the formula to your actual column name

    The IFs in the beginning first check if the row is a parent row, and that it's a Parent and not a Grandparent (your top row). The next IF only will list the next task if there are any incomplete tasks. If all the Child rows are Complete then the field will remain blank. The INDEX/COLLECT duo brings the data that you are interested in.

    Will this work for you?

    Kelly

  • LSxx24
    LSxx24 ✭✭

    Hi Kelly,

    Thanks so much for your response! I have this a try, and it seems to work but only for tasks that are 1 layer down. I guess my example screenshot was misleading as I didn't realize the number of layers would matter. Some things are quite nested with many layers of hierarchy, so we're looking for something that will work on any task that has other tasks indented below it - I hope that makes sense!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @LSxx24

    Yes, what you're saying makes perfect sense. The top row is the one I was trying to ignore, which has a level=0. If you wanted to see all of the next tasks, all in one cell, it could be done in that top row. But below should work to gather the info you requested

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) >0, [Parent Helper]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN([Task Name]@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

    Will this work for you?

    Kelly

  • LSxx24
    LSxx24 ✭✭

    Kelly - yes this is amazing, thank you so much for your help! Seeing this work now, I think we'll also add Assigned To, so we have who is responsible for that next task! :)

    This gives us the next task name due, can this be modified to also give us the matching due date? So we have next task due & its due date pulled through.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @LSxx24

    The first range in the COLLECT specifies the data you are pulling.

    This will give you the date.

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 2, [Parent Helper]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN(Finish@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

    In a helper Assigned To column (contact field) , you can pull in the Assigned To names

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 2, [Parent Helper]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN([Assigned To]@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

    *change the name of the field to match your column name, if necessary

    Will these work for you?

    Kelly

  • LSxx24
    LSxx24 ✭✭

    Hey Kelly,

    Thanks for explaining the first range in the COLLECT is what it will pull through - trying to learn all of this to do myself :) that makes sense

    Here is an updated screenshot of my sheet. It seems like the formulas are only working sometimes? I have them set as column formulas.

    Here is what is in each -

    Next Action Task Name

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) > 0, [Parent Row]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN([Task Name]@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

    Next Due

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 2, [Parent Row]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN(Finish@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

    Next Due Owner

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 2, [Parent Row]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN(Ownership@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @LSxx24

    Sorry you're having trouble. I see I forgot to change the level when I copied/pasted your additional formulas. You need to make the hierarchical levels of the additional formulas greater than 0, not equal to 2.

    Next Due

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) >0, [Parent Row]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN(Finish@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

    Next Due Owner

    =IF(AND(COUNT(ANCESTORS([Task Name]@row)) >0, [Parent Row]@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN(Ownership@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN(Finish@row), ISDATE(@cell), CHILDREN(Finish@row), @cell = MIN(@cell)), 1)))

    Would you mind sharing another screenshot, this time also including your Status column? If you look at the IF statements in the beginning of the formula, you will see that all of those conditions must be true, or else the formula will leave the cell blank. If cells are blank then we must figure out which of the conditions caused that to happen.

    Kelly

  • LSxx24
    LSxx24 ✭✭

    Thanks! I've updated that one - they seem to be pulling through :) This is the full screenshot



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    @LSxx24

    So it looks like it's all working properly? Let me know if anything is wonky.

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!