Identifying successors of successors (and so on...)

PeteP

Current status: I've successfully generated a "Successors" column, listing direct successors of each row, using the following formula:

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

Current roadblock: My "successors" column only shows direct successors. I'm looking to create a column that lists ALL successors, all the way up the dependency tree (e.g. successors of successors, successors of successors of successors, etc.). Let's call this column "Genealogy." Any support with this formula would be greatly appreciated!

End use case: My end goal is to create a filter where I can enter a row number (let's call it 'n'), and my sheet will display ALL rows that include 'n' in their "Genealogy" column. This would essentially display the critical path to row 'n', all the way up its dependency tree.

Paul Newcome

Are you looking to show everything that leads up to "Row 15", everything that comes after "Row 15", or both? I ask because you start with successors and successors of successors which indicates everything after, but then you talk about the "critical path" leading up to "Row 15".

PeteP

@Paul Newcome, thanks for the question! Hopefully this picture helps clarify. (all orange cells are manually entered here.)

Then, for example, if I'm looking to show the critical path to "Task 4", I could filter the "Successor Tree" column for cells that contain '4'.

Paul Newcome

Ok. So you want everything that leads up to "Task 4". Got it. I haven't quite figured it out yet, but I am working on it.

PeteP

Thanks @Paul Newcome! I'm realizing that another way to frame this request is as a filter for the Driving Path to a particular task row. I'm aware of the "Show Driving Path" tool, but haven't found a means of filtering for driving path.

Paul Newcome

There is an ISCRITICAL function you can use to check a box in a column for all rows that are on the critical path which would then allow you to create a filter for the critical path based on the display results of the critical path tool.

https://help.smartsheet.com/function/iscritical

PeteP

@Paul Newcome here's an example of why the ISCRITICAL function doesn't quite get me what I'm looking for:

(orange cells manually entered here for the example)

In this example, my "Successor Tree" column identifies all successors all the way down the dependency tree. Then, if I'm interested in only showing the driving path to Task A4 (row 5), all I need to do is filter the "Successor Tree" column for anything that contains row 5.

Note though that Task A4 is not on the project's critical path, so filtering by the "ISCRITICAL" column doesn't solve the problem.

Building the function to populate the "Successor Tree" column is really what I'm after here.

PeteP

Hey @Paul Newcome, happy Friday! Any luck on this post? I still haven't gotten there with my own experiments.

Genevieve P.

Hey @PeteP

There currently isn't a function that will automatically list all previous predecessors associated with the one task. Please submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community.

Cheers,

Genevieve

PeteP

@Genevieve P., understood - just created a post:

https://community.smartsheet.com/discussion/119756/modified-successors-function-to-report-more-than-just-direct-successors