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!!
Answers
-
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
-
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!
-
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
-
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.
-
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
-
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)))
-
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
-
Thanks! I've updated that one - they seem to be pulling through :) This is the full screenshot
-
-
@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)))
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
