Formula to identify the task name of next child task due

LSxx24

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!!

Kelly Moore

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

https://community.smartsheet.com/discussion/comment/412121#Comment_412121

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

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

https://community.smartsheet.com/discussion/comment/412425#Comment_412425

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

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

https://community.smartsheet.com/discussion/comment/412911#Comment_412911

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

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

https://community.smartsheet.com/discussion/comment/412919#Comment_412919

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

Kelly Moore

@LSxx24

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

Kelly

Kayla Q

@Kelly Moore I tried something similar, but I'm getting a blank return. Can you help me out?

Formula: =IF(AND(COUNT(ANCESTORS([Task Name]@row)) > 0, isParent@row = 1), IF(COUNTIFS(CHILDREN(Status@row), "Complete") < COUNT(CHILDREN([Task Name]@row)), INDEX(COLLECT(CHILDREN([End Date]@row), CHILDREN(Status@row), @cell <> "Complete", CHILDREN([End Date]@row), ISDATE(@cell), CHILDREN([End Date]@row), @cell = MIN(@cell)), 1)))

Kelly Moore

Hey @Kayla Q

Sorry for the delay. Looking at your formula, and your screenshot, the row in question does not appear to be a parent row based on the lack of checkmark in your 'isParent' column. Your Index formula will only run when the other IFs are true. The cell will remain blank with a false.

Kelly