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
Check out the Formula Handbook template!