I need help automatically changing status of all successors based on the status of a deliverable
Hello - can anyone help me identify a formula that will automatically change the status of every deliverable's successors depending on the status of that deliverable.
So, for example, when line 4 in the example below is 'at risk' in the 'Status' column, I also need lines 6 and 7 to automatically turn to 'yellow' in the same column (far right).
I have the 1st part working - when a deliverable is 'at risk', the status turns yellow, but I am unable to determine a formula for making all of its successors also turn yellow.
Here 's the formula I am currently working with, as well as some relevant notes:
- Successor Status Column: IF(AND(Status@row = "at risk", Successors@row <> ""), IF(HAS([Successor 1]:[Successor 10], [Row #]@row), "Yellow"))
- I only want a deliverable to turn 'yellow' in the 'Successor status' column if it has successors listed in the 'Successors' column. Otherwise, I want it to stay blank.
- Columns 'Successor 1', 'Successor 2'… through 'Successor 10' are helper columns to itemize the 'Successors' columns
- When actually built, my plan will eventually have 6 hierarchies
Thank you, in advance, to everyone who takes some time to try to help!
Best Answer
-
Hi @blawrence13
To reference Predecessors' Status in a Successors' row, using Prodecessors values in the row is more direct than using Successors value.
To get the Predecessors' value, in the June 5 comments to your question, I used the Text functions, such as SUBSTITUTE, FIND, MID, etc., to retrieve Prerecessor value from the Predecessors format like "3FS -5d", "8SS +4d".
In the demo solution above, I used the SUCCESSORS function's features of just returning the Row Number of the Sucessors to get Predcessor values.
The formulas are shown in the Sheet Summary of the published demo sheet.
With the Predecessor values of a given Successor row, you can populate Predecessors Status, Status Pred 1 to 10, in my case, with a formula like this;
[Status Pred 1] =IFERROR(INDEX(Status:Status, [Pred 1]@row), "")
Then, for the following [Status Pred 2] etc, you change the [Pred 1] to [Pred 2], etc.
With the Predecessors Status helper columns, you can determine the [successors status] with a formula like this;
[successors status] =IF(HAS([Status Pred 1]@row:[Status Pred 10]@row, "At Risk"), "Yellow")
There may be a better way, but getting the Predecessors' value of a given successor row is crucial here.
Answers
-
Hi @blawrence13
To reference Predecessors' Status in a Successors' row, using Prodecessors values in the row is more direct than using Successors value.
To get the Predecessors' value, in the June 5 comments to your question, I used the Text functions, such as SUBSTITUTE, FIND, MID, etc., to retrieve Prerecessor value from the Predecessors format like "3FS -5d", "8SS +4d".
In the demo solution above, I used the SUCCESSORS function's features of just returning the Row Number of the Sucessors to get Predcessor values.
The formulas are shown in the Sheet Summary of the published demo sheet.
With the Predecessor values of a given Successor row, you can populate Predecessors Status, Status Pred 1 to 10, in my case, with a formula like this;
[Status Pred 1] =IFERROR(INDEX(Status:Status, [Pred 1]@row), "")
Then, for the following [Status Pred 2] etc, you change the [Pred 1] to [Pred 2], etc.
With the Predecessors Status helper columns, you can determine the [successors status] with a formula like this;
[successors status] =IF(HAS([Status Pred 1]@row:[Status Pred 10]@row, "At Risk"), "Yellow")
There may be a better way, but getting the Predecessors' value of a given successor row is crucial here.
-
Thank you, jmyzk! I cannot adequately express how helpful this is and how appreciative I am of your time and efforts.
Thank you!!
BDL
-
Happy to help, @blawrence13
I learned much about getting predecessor values by answering your questions.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!