Status with Predecessor
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")))
Best Answer
-
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.
Answers
-
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.
-
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.
-
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")))
-
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 !
-
Yay! Glad my solution helped Happy New Year!😁
-
This is fantastic! Thanks for the help!
-
@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 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.
-
What do you mean by "my titles having start and end dates?"
Can you share an image?
-
@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?
-
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.
One way to keep the original parent's dates is to use the baseline feature.
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.
-
@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.
-
Unfortunately, it is not possible.
You need to use separate columns or rows for each character.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
