Formula for Showing Next Task and Owner in the Parent Row

Luke W.

Hello,

I'm looking for a way to show the next task and owner of that task in the parent row heading.

Example:

The formula is needed in the Parent Row of the Next Task column. It pulls the next task coming up based on date. I'm assuming a second formula is needed for the owner column where it will match the owner of the next task.

Any help would be greatly appreciated!

Thanks.

Paul Newcome

Try this adjustment:

=IFERROR(INDEX(CHILDREN([Task Name]@row), COUNTIFS(CHILDREN(Finish@row), @cell< TODAY()) + 1), "All Tasks Complete")

Paul Newcome

I would suggest not worrying about the date at all and just using "Complete".

=IFERROR(INDEX(CHILDREN([Task Name]@row), COUNTIFS(CHILDREN(Status@row), @cell = "Complete") + 1), "All Tasks Complete")

Paul Newcome

You would use something like this:

=IFERROR(INDEX(CHILDREN(Task@row), COUNITFS(CHILDREN(Start@row), @cell< TODAY()), "All Tasks Complete")

Luke W.

https://community.smartsheet.com/discussion/comment/375095#Comment_375095

Hey Paul, thanks for the response and the help! I really appreciate it.

That gives me an #UNPARSEABLE. I tried to fix it, but couldn't get it right.

Let me try asking it a different way...

Adding in some additional details might help make the desired solution more clear. I'm looking to add the next task that is due into the VNT column at the parent level.

I would also like to add the owner of that task into the Owner column at the parent level.

As highlighted below. I hope that helps clarify things.

Paul Newcome

It looks like the column name has changed from Task to Task Name. Try updating the formula to include the column names you are actually using in the sheet.

Luke W.

This is where I'm at:

=IFERROR(INDEX(CHILDREN([Task Name]@row), COUNITFS(CHILDREN(Finish@row), @cell< TODAY()), "All Tasks Complete")

Paul Newcome

Looks like I also fat fingered the COUNTIFS function.

=IFERROR(INDEX(CHILDREN([Task Name]@row), COUNTIFS(CHILDREN(Finish@row), @cell< TODAY()), "All Tasks Complete")

Luke W.

Now getting a #INCORRECT ARGUMENT SET

Paul Newcome

I need more coffee...

=IFERROR(INDEX(CHILDREN([Task Name]@row), COUNTIFS(CHILDREN(Finish@row), @cell< TODAY())), "All Tasks Complete")

Luke W.

EDIT**

Haha! That did work, but is pulling the first task, not the next task due. I'll keep chewing on it, but any suggestions?

And for the matching owner of the task to show in the Owner Column parent row...that has to be similar, correct?

Luke W.

@Paul Newcome - Any additional suggestions from the comment above?

Paul Newcome

Are you wanting to go based on date or percent complete?

Luke W.

https://community.smartsheet.com/discussion/comment/375342#Comment_375342

Date

Paul Newcome

Try this adjustment:

=IFERROR(INDEX(CHILDREN([Task Name]@row), COUNTIFS(CHILDREN(Finish@row), @cell< TODAY()) + 1), "All Tasks Complete")

Luke W.

That did it, Paul! Thank you very much!

Huge Help!

Luke

Paul Newcome

Happy to help. 👍️

Luke W.

@Paul Newcome

Ran into a snag when using this on my live data. Not sure what is happening as it worked on my test sheets.

The current formula is not pulling the task that it should be. See below:

Maybe another condition needs to be added?