Health Ripple Effect Formula

GCruz

On my project plans, I use a Red/Yellow/Green/Gray column called [Health], and I also use the stock [Predecessors] column. I'm trying to come up with a column formula so that if I change the [Health] value on a given row, it will update the [Health]@row value of every row downstream that directly or indirectly uses the changed row as a predecessor. I was able to come up with this column formula so far:

=IF([Predecessors]@row <> "", INDEX(COLLECT([Health]:[Health], [Row Number]:[Row Number], [Predecessors]@row ), 1), "")

Basically the formula will return the [Health] value of whatever row matches the [Row Number] value displayed in the [Predecessors] column for the row in question. It functions, but will only update the Health value for 1 step downstream. I realize that my [Health] column can't be both a user-input column and a column formula column. I was thinking maybe of having a helper column called [Health Change?] and having it update with the new [Health] value. Then I can use conditional formatting to alter the formatting of the [Health] column and alert me of the change.

So basically I'm looking for something where if [Task]2 uses [Task]1 as a predecessor, [Task]10 uses [Task]2 as a predecessor, and [Task]155 uses [Task]10 as a predecessor, then [Health Change?]2, [Health Change?]10, & [Health Change?]155 all get updated if [Health]1 changes.

With this example and using the formula I have above, it currently only updates [Health Change?]2 and leaves the rest of the downstream rows unchanged.

Hopefully I'm explaining this clearly, but please let me know if my ask is confusing. Thanks in advance for any help you can provide.

jmyzk_cloudsmart_jp

Hi @GCruz

A solution I created uses the ISCRITICAL function.

The demo sheet below is based on the project sheet of the Construction and Facility Management template.

The [Critical] column uses the following formula;

=ISCRITICAL([Task Name]@row)

For example, Gather Permits 1 is not critical, whereas Gather Permits 2 is critical. If I change this task's duration from 14d to 21d, the dates of all subsequent tasks will update accordingly.

Whether a task is critical or not is dynamic. If I change the duration of Gather Permits 1 to 15d, the task will become critical, while Gather Permits 2 will become non-critical.

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

In the [Health Ripple Effect] column, I used the following formula;

=IF(Critical@row, INDEX(Health:Health, MIN(COLLECT(Row:Row, Critical:Critical, true))), Health@row)

In the formula, 'MIN(COLLECT(Row:Row, Critical: Critical, true))' determines the lowest row number of the row whose [Critical] is true.

As the date example above shows, the top 'isCritical' rows affect subsequent tasks, so I consider it natural to use the row's Health value as the ripple-effect origin.

For the non-critical row, I set the formula to show its own health value. For example, in the Clearance and Clearance 2 cases, the Clearance 2 task has no successors, so I determined that its health status should be determined by its project status.

In the project sheet below, I changed the duration of Gather Permit 1 to 21d. The task became critical and affected the succeeding tasks' ripple effects on health, turning them red (the health of the Gather Permit 1 task).

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

Note: to demonstrate critical and non-critical tasks, I added rows with [Row ID] 18 and 19, and I added the Row column with the following formula to return the row number used in the INDEX function.

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)