Status with Predecessor

Barton Black

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

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

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

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

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

Asyraf

Hi @jmyzk_cloudsmart_jp ,

I have managed to incorporate your solution in my Project Plan. That's really a new gift got me 😊

Thank you so much for your support and Happy New Year !

jmyzk_cloudsmart_jp

@Asyraf

Yay! Glad my solution helped Happy New Year!😁

Barton Black

This is fantastic! Thanks for the help!

Hannah_Trousdale45

@jmyzk_cloudsmart_jp Hi, I am having trouble connecting the tasks with the arrows even with my formulas. Are the gantt chart arrows produced by the status formula?

jmyzk_cloudsmart_jp

Hi @Hannah_Trousdale45

To show arrows, you need to activate dependencies.

https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors

Hannah_Trousdale45

@jmyzk_cloudsmart_jp Is there a way to enable predecessors without using dependencies via formula? I don't want my titles having start and end dates and there isn't a way to get rid of them.

jmyzk_cloudsmart_jp

@Hannah_Trousdale45

What do you mean by "my titles having start and end dates?"

Can you share an image?

Hannah_Trousdale45

@jmyzk_cloudsmart_jp Yes, here they are. If I enter in a date for 1.1, it automatically sets the start date for the Finance categories. Is there a way to have certain rows be skipped?

jmyzk_cloudsmart_jp

@Hannah_Trousdale45

Smartsheet automatically rolls up the parent's start and end dates if you enable the dependencies feature.

The Start Date for a parent is determined by the earliest start date among all of its child rows. Similarly, the End Date is determined by the last end date among all its child rows.

https://help.smartsheet.com/articles/765753-parent-rollup-functionality

One way to keep the original parent's dates is to use the baseline feature.

https://help.smartsheet.com/articles/2482093-baselines

Or you may want to create columns for the parent's date.

In the image below, I changed Child 1's start date after I set the baseline and Parent Start and End.

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

Hannah_Trousdale45

@jmyzk_cloudsmart_jp Do you if there's any way, I can make 4 different types of text a different color in a single cell using conditional formatting? Please see the image for an example: I want R, A, C, and I to have different colors.

jmyzk_cloudsmart_jp

Unfortunately, it is not possible.
You need to use separate columns or rows for each character.