Status with Predecessor

Barton Black · 12/29/23

Hey All,

Need help with a formula. What I have works, but I want to add another condition to it though I am not sure how. I do not want the status to show as ready unless the predecessor is complete. I'd like a copy/paste formula vs referencing to a specific row. Thoughts? Here is the current formula

=IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Ready")))

jmyzk_cloudsmart_jp · 12/30/23

Hi @Barton Black

I would add a couple of helper columns for this.

Single Predressors Case

First, you can get the row number value of the Predecessors if there is only one Predecessor. (The formula converts the Predecessor value to a text and then to a number.)

[Pred] =VALUE(Predecessors@row + "")

You can get the Predecessor's [% Complete] using the value. (If you reference the Predecessor's [Status], you will get a circular reference error in the following formula in the [Status] column, so we use [% Complete] instead)

[Pred % Complete] =IF(Pred@row > 0, INDEX([% Complete]:[% Complete], Pred@row))

Finally, you can use the [Pred % Complete] in your formula as an additional condition.

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

Multiple Predressors Case

The example below tries to cope with multiple predecessor cases.

For example, if the Predecessors value is "2,3" as shown in the 4th row in the image below, you can not use the above VALUE(Predecessors@row + "") formula. You must get individual predecessor values using the text functions, such as FIND, LEFT, etc.

If your project sheet needs to cope with multiple Predecessors, please reference the column formula of the published dashboard sheets. Otherwise, try the above formulas.

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

jmyzk_cloudsmart_jp · 12/30/23

Hi @Barton Black

I would add a couple of helper columns for this.

Single Predressors Case

First, you can get the row number value of the Predecessors if there is only one Predecessor. (The formula converts the Predecessor value to a text and then to a number.)

[Pred] =VALUE(Predecessors@row + "")

You can get the Predecessor's [% Complete] using the value. (If you reference the Predecessor's [Status], you will get a circular reference error in the following formula in the [Status] column, so we use [% Complete] instead)

[Pred % Complete] =IF(Pred@row > 0, INDEX([% Complete]:[% Complete], Pred@row))

Finally, you can use the [Pred % Complete] in your formula as an additional condition.

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

Multiple Predressors Case

The example below tries to cope with multiple predecessor cases.

For example, if the Predecessors value is "2,3" as shown in the 4th row in the image below, you can not use the above VALUE(Predecessors@row + "") formula. You must get individual predecessor values using the text functions, such as FIND, LEFT, etc.

If your project sheet needs to cope with multiple Predecessors, please reference the column formula of the published dashboard sheets. Otherwise, try the above formulas.

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

Asyraf · 12/31/23

Hi @jmyzk_cloudsmart_jp ,

Thank you for the ideas and work around shared here. It awesome!

What if we have with 3 Predecessors and some of it with the application of Lag details?

Would much appreciate if we can have a solution for this.

Thank you.

jmyzk_cloudsmart_jp · 12/31/23

Hi @Asyraf

3 Predecessors

First, I added two helper columns to find the "," position.

[Find , 1] =FIND(",", Predecessors@row)
[FInd , 2] =IF([FInd , 1]@row > 0, FIND(",", Predecessors@row, [FInd , 1]@row + 1), 0)

Then, using those FIND values, I 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(Predecessors@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)

The application of Lag details

The lag details do not affect which row is the predecessor, so I removed unnecessary parts with the TEXT functions.

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

As the lag details take the form of "2FS +4d, 3SF +3d", we can determine if the Predecessors@row contains "F" or "S". By this check, we can cope with the Predecessors value such as 5SS or 7FF, etc. without lag, as well.

The formula removed the non-row number part by converting "S" to "F" and getting the row number before the first F position.

Finally, the Status column formula now has the third condition.

=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