Formula to Capture Current Task Start Date and Next Task

Vivien Chong

As an extension to previous question asked here https://community.smartsheet.com/discussion/comment/406009#Comment_406009

I would like to ask what is the formula that we can use to capture the current task start date and next task.

Current Milestone:

=INDEX(COLLECT([Task Name]:[Task Name], Milestone:Milestone, @cell = 1, Status:Status, @cell <> "Complete"), 1)

Current Task:

=INDEX(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete"), 3)

With reference to the above screenshot, my question is:

1) Since we have the current task formula above, I believe we can also capture the Start Date for the current task. How can we write the formula for this?

2) Using the successor formula, I would be able to get the Next Task name, how to write the formula to capture the next task base on the "Current Task"?

• The Current Milestone, Current Task, Current Task Start Date, Next Task is for reporting purpose at the Intake Sheet.

3) The Next Task Name formula doesn't seem to show the next task for rows with more than 1 successor. How can I improve on the formula for this? (formula taken from https://community.smartsheet.com/discussion/84257/successors-testing/p1)

Appreciate any assistance. Thank you in advance.

Paul Newcome

Lets try this...

Replace the delimiter in the JOIN function in the [Next Task] column with CHAR(10) for a line break and change the column type to a multi-select dropdown.

=JOIN(COLLECT(...............), CHAR(10))

Then we can use an INDEX/MATCH to grab the string of successors based on the [Current Task]. Since the [Next Task] column is now a multi-select dropdown, we can use the HAS function in a JOIN/COLLECT which should give us what we need.

=JOIN(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Row:Row, HAS(INDEX([Next Task]:[Next Task], MATCH([Current Task]@row, [Task Name]:[Task Name], 0)), @cell))

Paul Newcome

Are the current milestone and current task pulling in correctly, or do we need to start there?

Vivien Chong

Hi @Paul Newcome the current milestone and current task is pulling correctly.

I thought with the same concept, I should be able to capture the date and next task. However, am unable to get it right.

And the "Next Task Name" is not capturing the next task when there are more than 1 item.

Appreciate advice and assistance.

Paul Newcome

[Current Task Start Date] should be doable with a standard INDEX/MATCH when matching on the Current Task.

[Next Task.] would require a helper checkbox column with the following column formula:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1)

Then in the [Next Task.] field you could use something along the lines of

=INDEX(COLLECT([Task Name]:[Task Name], Helper:Helper, @cell = 1, Status:Status, @cell <> "Complete"), 2)

Vivien Chong

Hi @Paul Newcome I manage to work out the [Current Task Start Date] base on your tips. ;)

However, for the next task, I do not understand how your formula works as it should be dependent on the dependencies relationship.

In my screenshot, I am trying to display [Next task Name] with this formula:

=IF([Next Task]@row <> "", JOIN(COLLECT([Task Name]:[Task Name], Row:Row, HAS([Next Task]@row, @cell)), CHAR(10)))

But it only display task names when there is only 1 task, but the dependencies is linked to few tasks, it shows as blank. Do you have any idea how to work on this?

I think if I can get this [Next Task Name] right, I should also be able to use the Index/Match formula to surface that to [Next Task.]row 1.

Paul Newcome

Lets try this...

Replace the delimiter in the JOIN function in the [Next Task] column with CHAR(10) for a line break and change the column type to a multi-select dropdown.

=JOIN(COLLECT(...............), CHAR(10))

Then we can use an INDEX/MATCH to grab the string of successors based on the [Current Task]. Since the [Next Task] column is now a multi-select dropdown, we can use the HAS function in a JOIN/COLLECT which should give us what we need.

=JOIN(COLLECT([Task Name]:[Task Name], Status:Status, @cell <> "Complete", Row:Row, HAS(INDEX([Next Task]:[Next Task], MATCH([Current Task]@row, [Task Name]:[Task Name], 0)), @cell))

Vivien Chong

Hi @Paul Newcome thanks for your insight. I manage to get the next task to surface on row 1. Appreciate your expert guidance to get the formula right.

Paul Newcome

Happy to help. 👍️