A True Mind Bender - Tracing Tasks through Predecessors

Berto_DA

Is this even possible? I have a task (say Task 18) that is preceded by other tasks, all joined by predecessors. Task 18 is the "final" task.

Task #18 is preceded by Task 16, and Task 16 is preceded by Task 14.

Outcome - I want to filter rows to see only the tasks that have a relationship to "Task 18."

I'm thinking that I need a formula that begins with the final task in this relationship - Task 18 - looks for its predecessor (Task 16) checks off a box at Task 16, then looks to see if Task 16 has predecessor… and repeats.

A major wrinkle - There are other tasks that have predecessors. But I want to filter only those related to Task 18. So I need some logic to do that.

— — —

This is what a Before and After would look like…

And after applying the filter, things look like this…

A free beer to anyone who can solve that!

Provided you live near me…

SoS | Dan Palenchar

Hello @Berto_DA,

I've noticed this sticking around unanswered for a while so I took a stab at it. It is indeed very tricky but I have something that I think will work for your exact scenario (but with some serious limitations).

Solution

What I did was use the SUCCESSORS() function to pull to, naturally, the successors of each row. From there, I made a series of INDEX(MATCH() formulas to pull out the Successor of a given row's Successor based on the Row ID. I did this 9 times because this is what the number of relationship in your Sheet requires.

The result is something like this

The S column has the formula:

=JOIN(SUCCESSORS(Task@row), ", ")

The various S# columns have a formula structured as below where the bold # refers to the S# column the left (or the S column itself in the case of S2:

=IFERROR(INDEX(S:S, MATCH(VALUE([S#]@row + ""), [Row ID]:[Row ID], 1)), "")

So, for example, the formula in S4 is:

=IFERROR(INDEX(S:S, MATCH(VALUE([S3]@row + ""), [Row ID]:[Row ID], 1)), "")

From here, I made a multi-select dropdown formula to pull all of the Successors into one column using:

=JOIN(S@row:[S9]@row, CHAR(10))

To make this filterable based on Task 18 (or any row) I made these additions:

1. Add a Sheet Summary Field called Check relationship where you can select a number,
2. Add the Check column with formula =IF(HAS([All Successors]@row, [Check relationship]#), 1, 0)
3. Make below filter
   

The result of this is you can put any number you want into Check relationship then apply the filter to see the rows that are related. I also added conditional formatting to highlight the All Successors column if the Check applies so it can be easily seen without filter.

Put it all together, and you get this published Sheet which is also below (you can interact with this).

https://app.smartsheet.com/b/publish?EQBCT=6f51dfa7943d4b5fb5c07359619fa0e8

Here are screenshot outputs of the whole sheet unfiltered/ filtered

Limitations

This won't work if you have multiple direct successors in one row as it will display both of them with a comma in the various S fields, such as this

I imagine this will break down as predecessor relationships get more complex, but have not tested it thoroughly.

Finally, you will need to keep adding S# columns for each additional level of relationship.

***

Hope this helps! If anything it was an interesting problem to try and solve. I do think with some more time a thorough solution could be developed to handle more complex examples.

Berto_DA

Holy Cow! This is… incredible. It will take me some time to digest this and apply. What can I say? Thank you!

jmyzk_cloudsmart_jp

Hi @Berto_DA

Here is another solution.

https://app.smartsheet.com/b/publish?EQBCT=5c5a8b3d7c2544138616dba943999c58

Use the ISCRITICAL function.

An easy way to get all the Predecessors is to use the ISCRITICAL function.

In the image above, the checkbox column [is Critical] has this formula;

=ISCRITICAL([Task Name]@row)

Then, you can use the column's values to filter the Predecessors rows.

The limitation is that this solution does not show non-critical Predecessors. For example, Task 3 is a Predecessor of Task 18 or The Final task, but not shown in the image above, as the task is not critical.

Filter the Predecessors of a given task, including non-critical ones.

Another limitation of the above ISCRITICAL approach is that you can not use the function to filter the Predecessors of any given task. In a practical PM situation, you may want to check all the task's predecessors quickly to see what tasks affect it. For example, if a project has hundreds of tasks, this feature may come in handy.

Like @SoS | Dan Palenchar , I used the SUCCESSORS function, but in a different way. I learned the merit of using the function answering the question of @blawrence13. (See the bottom for the QA).
The function's merit is that unlike the Precessors column, which has a difficult-to-treat format like "3FS -5d" or "8SS +4d," it returns only row numbers, like 3, 4, 5, and 8, if you JOIN the result.

The basic approach is first to get the row number of the selected task, 12, in the image above. Then, go to each row and check the JOINed SUCCESSORS values to see if the selected task's row number is included. For example, the 8th row's JOINed SUCCESSORS values CONTAINS 12, so I checked p1. (Likewise, the 5th row.) Then, using those results, a similar check is repeated for the Predecessor's Predecessor. For example, in the example above, 8 and 5 are checked as p1, and we find the 4th row's JOINed SUCCESSORS values CONTAINS 8 and 5, and the 3rd row CONTAINS 5, so check those row's p2. (This is similar to what you suggested in your question.)

I'm thinking that I need a formula that begins with the final task in this relationship - Task 18 - looks for its predecessor (Task 16) checks off a box at Task 16, then looks to see if Task 16 has predecessor… and repeats.

Finally, with all the p1 to p10 (in this solution) checked, we can determine all the Predecessors. of a task.

Formulas

[_ID_] =DL# + ID@row + DL#

I used a helper column "_ID_" so that when I check 3, for example, with the CONTAINS function, I want only to check the 3, not 13, 23, or 31. With the function, they become "_3_", "_13_", so if I check CONTAINS("_3_", "_2_3_"), it return true but if I check CONTAINS("_3_", "_12_13_"), it returns false. (The DL# is the delineator character I specified in the Sheet Summary field.)

In the above image, the Selected Task Row No gets the row number sandwiched with the DL#. The formula above means if the [Select Task} column has true values more than 0, give me the last [_ID_] value of the row whose [Select Task] column is checked. (This ensures that if a user checks more than one row of the [Select Task], we want to determine only the latest one.)

[_SUC_] =DL# + JOIN(SUCCESSORS([Task Name]@row), DL#) + DL#

This formula returns values like "_7_8_9_."

[p1] =IFERROR(IF(CONTAINS([Selected Task Row No]#, [_Suc_]@row), 1), "")

The formula checks the JOINed SUCCESSORS values, [_Suc_]@row). to see if the selected task's row number,[Selected Task Row No]#, is included.

[p2] =IFERROR(IF(COUNTIFS(COLLECT([_ID_]:[_ID_], [p1]:[p1], 1), CONTAINS(@cell, [_Suc_]@row)) > 0, 1, 0), "")

The COLLECT function gets a range of [_ID_] column values checked. Then, the CONTAINS function checks if each range value is contained in the [_Suc_]@row and returns 1 to check the column if true.

[p3] to [p10] has the same structure-function, only changing the [p#]:[p#]'s #.

for example

[p10] =IFERROR(IF(COUNTIFS(COLLECT([_ID_]:[_ID_], [p9]:[p9], 1), CONTAINS(@cell, [_Suc_]@row)) > 0, 1, 0), "")

Finally, we get the [Is Pred] checkbox formula as follows;

=IFERROR(OR([p1]@row, [p2]@row, [p3]@row, [p4]@row, [p5]@row, [p6]@row, [p7]@row, [p8]@row, [p9]@row, [p10]@row), 0)

As in the image above, you can use this value to create a filter to show the filtered tasks.

@blawrence13 's discussion

https://community.smartsheet.com/discussion/123258/i-need-help-automatically-changing-status-of-all-successors-based-on-the-status-of-a-deliverable