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

Options

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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

  • blawrence13
    Options

    Thank you, jmyzk! I cannot adequately express how helpful this is and how appreciative I am of your time and efforts.

    Thank you!!

    BDL

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Happy to help, @blawrence13

    I learned much about getting predecessor values by answering your questions.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!