SUCCESSORS Testing

Paul Newcome

I have seen in the past quite a few people asking how we can leverage predecessors for alerts so that when (for example) the preceding task is complete, a notification goes out automatically to the owner of the next task letting them know it is ready to start. The best approach to this was unfortunately parsing out the predecessors across multiple columns (if you had multiple predecessors).

I have been playing with the new SUCCESSORS function, and think I have almost found a way (with certain limitations). The limitations with this would be if you specified "SS", "SF", "FS", or "FF". I think I have some ideas on how to accommodate that but want to do some more testing before putting it out here for everyone.

In the mean time... Please feel free to take a look at the below to get started . I also welcome feedback and ideas on how to adjust for the other variables such as lag/lead time, "SS"/"SF"/etc., and any other "catch" people can think of.

First we insert an Auto-number column with no special formatting (called "Auto").

Next we insert a text/number column (called "Row") and use the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then we insert a multi-select dropdown column (called "Next Tasks") and used this column formula:

=JOIN(SUCCESSORS([Task Name]@row), CHAR(10))

From here we you can start to deviate and adjust as needed, but the below formula (put into another text/number column) will grab the % Complete for the previous tasks, add them up, then compare it to how many previous tasks there are. If those two numbers match, then the task on this row is ready to start.

=IF(AND(Predecessors@row <> "", [% Complete]@row = 0, SUMIFS([% Complete]:[% Complete], [Next Tasks]:[Next Tasks], HAS(@cell, Row@row + "")) = COUNTIFS([Next Tasks]:[Next Tasks], HAS(@cell, Row@row + ""))), 1)

.

In the below screenshot, you will see that Row 5 is ready to start because its predecessor (row 4) is at 100% complete, and row 5 has not yet been started. Row 9 is not yet ready because it needs both rows 7 & 8 to be completed first and 8 is only at 50%. You will also see that Row 8 is not marked as ready to start because even though its predecessor is completed, the task on Row 8 has already ben started (% Complete greater than zero).

Paul Newcome

To output the task name(s) for whatever is coming next (formula is in [Next Task Name] column):

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

Paul Newcome

@Vivien Chong Has a much cleaner solution if all you need is to pull the next task names.

=JOIN(SUCCESSORS([Task Name]@row), ", ")

I imagine this could also be adapted for pulling upcoming dates and whatnot as well...

Vivien Chong

@Paul Newcome Thanks for sharing this. I have not tested on scenario where there's more than 1 predecessor and have not tested with all the SS, SF, FF, lag... Will be following your thread for any updates. Thanks for sharing your insight.

Paul Newcome

@Vivien Chong If you do get around to testing it before I do, please feel free to share!

Vivien Chong

@Paul Newcome sure! but looking at the expertise level, I think you will find out the solution quicker than me. Good to have you around.

tariq.shaikh@signspecialists.com

@Paul Newcome and @Vivien Chong - You just made my day! Thank you for your insightful help!🤗

Paul Newcome

To pull the task name(s) of the actual predecessors, we would use the above instructions on generating the row number on the sheet. Next we would insert a multi-select dropdown column and enter

=SUBSTITUTE(Predecessors@row, ", ", CHAR(10))

Doing this allows us to "parse" those predecessors out into individual numbers as opposed to a text string. Finally we would use formula such as this to pull a listing of all of the predecessors:

=JOIN(COLLECT([Task Name]:[Task Name], Row:Row, HAS(Pred@row, @cell)), CHAR(10))

msamo

Hi Paul,

Thank you for the valuable information you have provided! I am having trouble getting the formula to work for me, is there any chance you could provide an example "dumbed down" for myself and others that might be struggling. The portion of the formula I am struggling with is in bold below. I do not know what should go there. What I am trying to do is return the task names of the successors of a selected task. Please help AND thank you in advanced!

fx=JOIN(SUCCESSORS([Task Name]@row), ", ")

Paul Newcome

@msamo You should be able to leave it as is assuming the column you want to join together is called "Task Name". Otherwise you would need to change "Task Name" to whatever the column is actually called.

Davide M.

HI, i've been looking into this thread to find a solution to display the task predecessor "Name" in a new column additional to the predecessor column.

I've followed the steps suggested above but the result is not what i'm expecting see picture.

Here below the formula behind each column, as I understood from above comments.

Auto = Auto numbering

Row = MATCH(Auto@row, Auto:Auto, 0)

New task = JOIN(SUCCESSORS([Task Name]@row), CHAR(10))

New Task Name = SUBSTITUTE(Predecessors@row, ", ", CHAR(10))

Thansk a lot for any help, i'm a 3 weeks SmartSheet user :)

Vivien Chong

@Davide M. I have just done a similar testing on the formula.

Task Name Row Successor Successor task name

I think for yours, you can try this

New task name =JOIN(Collect([Task Name]:[Task Name], Row:Row, HAS([New Task]@row, @cell)), CHAR(10))

Is this helpful?

Beverly Fields

Hi all,

We tried to add start/end times to our plan in Smartsheet, which we could calculate duration thanks to the wonderful community threads from folks like @Paul Newcome and others. I'm wondering if anyone has figured out how to have a formula look up a task's predecessors, confirm they are all complete (not unlike the formula for doing a check box for "Ready to Start"), but instead look at the end time for all predecessors that are complete and take the latest "end time" to fill in as the start time for the task.

See sample for row 5: If row 2 and row 4 are predecessors and are 100% complete, ideally we would want the start time for row 5 to be filled in as 1:07 AM automatically. We have so many rows and predecessors in the plan that it would be a lot to try to do manually. Any ideas/thoughts?

Thanks in advance for your ideas/input!

Beverly Fields

Hi all,

We were trying to include start and end time for a cutover plan in Smartsheet. I was able to get duration to calculate the time thanks for the other threads from folks like @Paul Newcome, but haven't quite cracked how we could get time to update based on predecessors.

Does any one have any ideas or thoughts on how we might be able to get the end time of a predecessor(s) to update as the start time of a successor task?

OR we were even just trying to get similar functionality like the "Ready to Start" check box above, where it looks at all of the predecessors for a row and confirms they are all 100% complete. In addition, we wanted to be able to look at those completed predecessors and take the latest "End Time" and make it/copy it as the "Start Time" for the successor task.

Sample: in Row 5, if Row 2 & 4 are completed, we would like the start time for Row 5 to automatically input: 1:07 AM. Not sure if that's possible, or if anyone has any ideas on how we could make that work.

Any help or ideas are much appreciated!

Thanks!

Paul Newcome

@Beverly Fields You will need to piece together a few different solutions.

First you want to convert all end times into numbers (probably already done somewhere if you are using formulas to generate the end time).

Then you want to use a MAX/COLLECT similar to the JOIN/COLLECT above to grab the highest number from all of the applicable predecessors.

Finally you would need to convert that MAX/COLLECT back into an actual time which you are also probably already doing.

Beverly Fields

@Paul Newcome Thanks, Paul! We will give that a try.