Formula help: Flag successors based on a deliverable's status?

Options

Hello all - Does anyone have any ideas on how to build a formula to flag individual successors based on the status of the initial deliverable?

For example, when I mark the status for row 200 "at risk", I would like all of its successors (201 & 202) to automatically turn yellow in my "Successor Alert" column (instead of the green circle which just shows that it has a successor), so I can easily see what else could also be sliding as deliverables slide?

For now, I just have flags which show that predecessors and/or successors exist (Alerts) and which rows correspond (Predecessor & Successors). I can't figure out how to make them interact with the status so I can pull them into a report showing the deliverable and their subsequent successor(s).

In case you need them, here are the formulas in my example below:

  • Predecessor Alert: IF(Predecessor@row <> "", "Yes")
  • Successor Alert: IF(Successors@row<> "", "Yes")

Any formula(s) / thoughts you can share are tremendously appreciated!

Thank you!

Brian

Answers

  • MarceHolzhauzen
    MarceHolzhauzen ✭✭✭✭
    Options

    Hi Brian

    Have you tried using the row reference method to add a row reference to each row, and based on the row reference, use match the predecessor / successor with the row reference and displaying the status of the relevant row?

    I am not sure if it will work for cells with multiple predecessors, as you can do single lookups only, but worth a try

    Marcé Holzhauzen
    Dare to try

  • blawrence13
    Options

    Thanks, Marcé.I will give that a try.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!