Need to see the 'Top level number' row in color when one of the WBS activities is at Risk.

Kiranb

Here In this image, I have an ECO with 5 WBS activities. I'm tracking activity based on [Planned Completion date] and Today(). Also gave a condition to change row color when a Flag is checked.

But I'm trying to change only 'ECO' row to red color instead of WBS activity row color. When any activity is at risk- a flag should be checked and The 'ECO' (top level) also should see the flag and only that row should be converted to red color. Can I get some suggestions here?

Request Smartsheet Master's here to help me….

Thanks

jmyzk_cloudsmart_jp

Hi @Kiranb

In the demo solution below, the [Child Risk] column gets flagged according to the children's Risk status with the following formula;

[Child Risk] =IF(COUNTIF(CHILDREN(Risk@row), 1) > 0, 1)

The demo sheet uses a standard way of managing the [Planned Completion date] by using the "Baseline" feature and uses the [Variance] value to determine the Risk status. Since the children's variance determines the baseline's parent row's variance, you do not have to create the [Child Risk] column as if you set the [Risk] according to the variance, the parent's Risk gets flagged if the children's overall variance is negative.

So, even if some of the children's tasks are at Risk, if the overall children's task does not have negative variance, the parent is not at Risk if you use this method. (For example, in the image below, Child 4 is one day behind, but Child 5 is one day ahead, so Parent 2 is not at Risk.)

https://app.smartsheet.com/b/publish?EQBCT=d00e7ca9fa534dd88b5c0b24a9661145 (Link to the published demo sheet)

Kiranb

Thanks for the Guidance, It worked.
But I would like to get a 'Risk' column formula instead of cell formula.
I would like to auto-manage the same formulas across multiple ECOs (individual Parents within the same sheet). How can I manage the formula automation across Parents and children differently in the same column?

Thanks in advance!

jmyzk_cloudsmart_jp

@Kiranb

As you can see in the demo sheet, all my formulas are column formulas, including the "Risk" column formula.

Please elaborate on your request.

In case you do not want to use the [Child Risk] column and apply a different formula to the [Risk] column depending on the row is parent or child, here is a modified demo sheet and formula;

[Risk] =IF(AND(ANC@row > 1, Variance@row < 0), 1, IF(AND(ANC@row = 1, COUNTIF(CHILDREN(Risk@row), 1) > 1), 1))

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