Get the status of predecessor tasks in Smartsheet (Community)

table of contents

first

Smartsheet allows you to automatically change the status of a self-task based on the completion status of its predecessor. By using this feature, you can more accurately understand the progress of tasks and streamline the planning and management of the entire project.

Question summary

Barton Black asks how to use Smartsheet's Status column formula to prevent self-tasks from displaying as "ready" even if their predecessors are not complete. (The original Community article isplease use this form.）

Answer summary

In answer to this question, jmyzk_cloudsmart_jp proposes a solution that accommodates both single and multiple predecessor tasks.

For a single predecessor task

For a single predecessor task, you can determine the completion status of the predecessor task by getting the row number of the predecessor task and looking at the value in the % Complete column for that row.

Specifically, use the following formula:

=IF(AND([% complete]@row > 0, [% complete]@row < 1), “in progress”, IF([% complete]@row = 1, “complete”, IF(AND([% Complete]@row = 0, [Pred % Complete]@row = 1), “Ready”)))

In this formula, the first AND function checks that the value in the invoking task's % Complete column is greater than or equal to 0 and less than 1. If this condition is met, the invoking task will be in the "in progress" status.

The following IF function checks that the value of the % Completed column of the invoking task is 1. If this condition is met, the invoking task will have a status of "Complete".

The final IF function checks that the value in the % Completed column for the invoking task is 0 and the value in the % Completed column for the predecessor task is 1. If this condition is met, the invoking task will have a status of "Ready".

For multiple predecessor tasks

If you have multiple predecessors, you must use a text function such as the FIND or LEFT function to obtain the line number of the predecessor.

Specifically, use the following formula:

=IF(AND([% complete]@row > 0, [% complete]@row < 1), “in progress”, IF([% complete]@row = 1, “complete”, IF(AND([% Complete]@row = 0, [Pred 1 % Complete]@row = 1, [Pred 2 % Complete]@row = 1, [Pred 3 % Complete]@row = 1), “Ready”)))

The following IF function checks that the value of the % Completed column of the invoking task is 1. If this condition is met, the invoking task will have a status of "Complete".

The final IF function checks that the value in the % Completed column for the invoking task is 0, and the value in the % Completed column for the predecessor task is all 1. If this condition is met, the invoking task will have a status of "Ready".

Summary

In Smartsheet, you can use the following methods to obtain the status of predecessor tasks:

For a single predecessor task: Get the row number of the predecessor task and refer to the value in the % Complete column for that row.
For multiple predecessors: Use text functions such as FIND and LEFT to obtain the line numbers of predecessors.

By combining these methods, you can respond to more complex conditions.

Translation of correspondence

Get the status of predecessor tasks in Smartsheet

burton black ✭
12/29/23

Hey guys, I need help with formulas. What I have works, but I don't know how, but I would like to add another condition. I don't want the status to show as ready unless the preceding tasks are completed. I need to reference a specific row rather than copy/paste the formula. What are your thoughts? The current formula is:

=IF(AND([% complete]@row > 0, [% complete]@row < 1), “in progress”, IF([% complete]@row = 1, “complete”, IF([% complete] @row = 0, "ready")))

best answer

jmyzk_cloudsmart_jp ✭✭✭✭✭✭

2023/12/30 Edited on December 2023, 12

Hello@Burton Black
Add some helper columns for this.
For a single prepressor
First, if there is only one predecessor, you can get the value of the predecessor's line number. (The formula converts the leading value to text and then to number.)

[Previous] =VALUE(precedent@row + “”)

You can use the value to get the [% Complete] of the predecessor. (Referencing the predecessor's [Status] will cause a circular reference error in the [Status] column in the following formula, so use [% Complete] instead.)

[Pred % Complete] =IF(Pred@row > 0, INDEX([% Complete]:[% Complete], Pred@row))Finally, you can use [Pred % Complete] as an additional condition in your formula.
=IF(AND([% complete]@row > 0, [% complete]@row < 1), “in progress”, IF([% complete]@row = 1, “complete”, IF(and([% complete]@row Complete]@row = 0, [Pred % Complete]@row = 1), “Ready”)))

For multiple prepressors
The example below attempts to deal with multiple antecedent cases.
For example, if the value of Predecessor is “4” as shown in the fourth row of the image below, the above VALUE(Predecessor@Row + “”) expression cannot be used. You must use text functions such as FIND, LEFT, etc. to retrieve the individual leading values.
If your project sheet needs to accommodate multiple predecessors, see Column Formulas for Public Dashboard Sheets. Otherwise, try the formula above.https://app.smartsheet.com/b/publish?EQBCT=5680f8df74e047c9b24e5b461ac677d2

answer

jmyzk_cloudsmart_jp ✭✭✭✭✭✭
2023/12/30 Edited on December 2023, 12

Hello@Burton Black
Add some helper columns for this.
For a single prepressor
First, if there is only one predecessor, you can get the value of the predecessor's line number. (The formula converts the leading value to text and then to number.)

You can use the [previous] =VALUE(predecessor@row + “”) value to get the [% Complete] of the predecessor. (Referencing the predecessor's [Status] will cause a circular reference error in the [Status] column in the following formula, so use [% Complete] instead.)
[Pred % Complete] =IF(Pred@row > 0, INDEX([% Complete]:[% Complete], Pred@row))Finally, you can use [Pred % Complete] as an additional condition in your formula.
=IF(AND([% complete]@row > 0, [% complete]@row < 1), “in progress”, IF([% complete]@row = 1, “complete”, IF(and([% complete]@row Complete]@row = 0, [Pred % Complete]@row = 1), “Ready”)))

Communication after answering

https://app.smartsheet.com/b/publish?EQBCT=5680f8df74e047c9b24e5b461ac677d

Ashraf ✭✭
2023/12/31
Hello@jmyzk_cloudsmart_jp ,

Thank you for the ideas and workarounds you shared here. That's amazing!
What happens if we apply lag details to three leading processors and some of them?
It would be very helpful if there is a solution for this.

thank you.

jmyzk_cloudsmart_jp ✭✭✭✭✭✭
2023/12/31 Edited on December 2023, 12

Hello@Ashraf
3 predecessor
First, I added two helper columns to find the position of ",".

[Search, 1] =FIND(“,”, predecessor@row)
[FInd , 2] =IF([FInd , 1]@row > 0, FIND(“,”, predecessor @row, [FInd , 1]@row + 1), 0) Then use these FIND values and added three helper columns.
[[Pred 1 Text]=IF([FInd , 1]@row > 0, LEFT(Predecessors@row + “”, FIND(“,”, Predecessors@row + “”) – 1), (Predecessors@row + “”))
[Pred 2 Text]=IF([FInd , 1]@row > 0, MID(Predecessors@row, [FInd , 1]@row + 1, IF([Find , 2]@row > 0, [Find , 2 ]@row – [FInd , 1]@row – 1, LEN(predecessor@row) – [FInd , 1]@row)))
[Pred 3 Text]=IF(OR(CONTAINS(“F”, [Pred 3 Text]@row), CONTAINS(“S”, [Pred 3 Text]@row)), LEFT([Pred 3 Text]@row , FIND(“F”, SUBSTITUTE([Pred 3 Text]@row, “S”, “F”)) – 1), [Pred 3 Text]@row)

Applying lag details
The lag details don't affect which lines precede, so I removed the unnecessary parts with the TEXT function.

[Pred 1] =IF(OR(CONTAINS(“F”, [Pred 1 Text]@row), CONTAINS(“S”, [Pred 1 Text]@row)), LEFT([Pred 1 Text]@row, FIND(“F”, SUBSTITUTE([Pred 1 Text]@row, “S”, “F”)) – 1), [Pred 1 Text]@row)

The lag details take the form "2FS +4d, 3SF +3d" so you can determine if Predecessors@row contains "F" or "S". This check allows it to support Predecessors values such as 5SS and 7FF without any lag.
This formula removed the non-line numbers by converting 'S' to 'F' and taking the line number before the first F position.
Finally, a third condition was added to the status column expression.
=IF(AND([% complete]@row > 0, [% complete]@row < 1), “in progress”, IF([% complete]@row = 1, “complete”, IF(AND([% Complete]@row = 0, [Pred 1 % Complete]@row = 1, [Pred 2 % Complete]@row = 1, [Pred 3 % Complete]@row = 1), “Ready”)))https://app.smartsheet.com/b/publish?EQBCT=e6317d033b4d4067b965418e3a316d52

Ashraf ✭✭

01/01/24

Hello@jmyzk_cloudsmart_jp ,

I was able to incorporate your solution into my project plan. I really received a new present 😊

Thank you very much for your support. And Happy New Year!