I need help automatically changing status of all successors based on the status of a deliverable

blawrence13

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!

jmyzk_cloudsmart_jp

Hi @blawrence13

To reference Predecessors' Status in a Successors' row, using Prodecessors values in the row is more direct than using Successors value.

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

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.

https://community.smartsheet.com/discussion/comment/439120#Comment_439120