SUCCESSORS Testing

Amanda O

Hi @Paul Newcome

Thanks for sharing and this is great, as this is something I am looking at, at the moment, did you get to a solution when you have SS or FS in your predecessor?

Paul Newcome

@Amanda O I honestly have not pursued this much since the original testing. I got crazy busy and my solutions rarely use predecessors which meant this had to get shelved for a while.

What exactly are you wanting to accomplish?

Neil Watson

Hi @Paul Newcome

I am trying to amend your solution for my case where the % Complete is a status where "Complete" is selected by the user when the task is done. Can you suggest a solution for this based on your formula below?

=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)

Paul Newcome

@Neil Watson Try changing the SUMIFS to a COUNTIFS and evaluating your status column for "Complete".

Neil Watson

Thanks @Paul Newcome, the final version ended up as:

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

We have a few tasks that are "SS" (as you mentioned, the above works for "FS") so will work to expand that, it should be relatively easy to track when the FS tasks are showing as "Started"....famous last words?

Vivien Chong

Hi @Paul Newcome, I followed your formula for "Ready to Start" so the next task's owner will be informed once the predecessor task is at 100%.

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

However, it doesn't reflect the next task correctly. Any idea what went wrong in my scenario?

Paul Newcome

@Vivien Chong What exactly do you have in the Successor column?

Vivien Chong

@Paul Newcome I have this in my Successor column: =JOIN(SUCCESSORS([Task Name]@row), ",")

Vivien Chong

Another option I am working on, to inform the next task person when predecessor task is completed.

I use this formula for the "NEXT PIC" column:

=IF(Sucessor@row <> "", JOIN(COLLECT([Assigned To]:[Assigned To], Row:Row, HAS(Sucessor@row, @cell)), CHAR(10)))

For example: Row 8 successor is row 11, the Assigned To is "57N-Sales".

I have a problem: When the Successor is more than 1, the "NEXT PIC" is empty.

I have allow "multiple contact per cell" for NEXT PIC column.

Any advice / input would be greatly appreciated.

Lisa Matthews

@Paul Newcome

Hello Paul,

I have set up the columns/formulas based on your excellent step-by-step guide. I think I might have missed something, can't figure out what it is. So far, it only works for rows where there is only one successor. How can I adjust the formula to extract the task name for multiple successors?

Thank you for your help.

Paul Newcome

@Lisa Matthews You would make the Next Tasks column a multi-select dropdown type column (make sure you are using CHAR(10) as the delimiter in that column formula) and then use a JOIN/COLLECT/HAS to join the task names together.

Lisa Matthews

@Paul Newcome

Thank you so much! I changed the Next Tasks column to a multi-select dropdown type column. The formula is working, for the most part. I'm encountering 2 problems now:

• Tasks which have the actual suffix (FS, SS, FF, SF) and/or lag. Not sure how to adjust the formula to remove these and return the row # only.

• Smartsheet does not like what I am doing. After I change the column type to allow multiple values per cell, Smartsheet will not save my sheet anymore. If I uncheck the column option for "allow multiple values per cell", I am able to save the sheet. Strange. Not sure if you have seen this before?

DonaraJaghinyan

Thank you all for your suggestions and examples.

I wonder if it will be possible to send a notification to the successor that the predecessor activity is complete and that they can proceed to working on their task?

tariq.shaikh@signspecialists.com

@DonaraJaghinyan - Try adding a Flag column and use a formula like this to trigger the flag when all predecessors are complete and the Successor is ready to start:

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

You can then use this Flag column to trigger notifications.

SPHRProject

@Paul Newcome Do you by chance have a sheet you can share where you have these formulas in action? I've been at this for days, called the help desk and of course did not get the help I needed. My brain is fried but I think that this will help me get what I need and solve another problem. I asked for a way to create a list/table of successors for each task that has a successor that would show the PMID of the Initial Task and the Task Name and return the successor RowID, PMID and Task Name and the pull-in key dates and assigned to information. I have multiple successors and dependencies. I need to be able to quickly say this workshop drives these tasks/objects and if there are any other objects impacted list those as well. I read through but again my brain is fried if I could see it in an actual sheet, it would really help. I had originally asked for it to just create a table PMID, Task Name, Successor Number, Task Name, for task that have 5 successors, each successor would be on its own row. I also have been trying to figure out how to provide the same information of if the predecessor tasks are complete then you can start your task. Any chance you'd mind sharing a sheet with these formulas setup? Sheri