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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!