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

blawrence13 · 05/29/24

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

jmyzk_cloudsmart_jp · 05/30/24

Hi @blawrence13

To effectively track the status of predecessors, it's essential to first retrieve the predecessor's row numbers.

The column for predecessors includes unique formats such as "3FS -5d", "3, 4", "5", "7", "8SS +4d", and "8, 9", featuring characters like "FS", "SS", "-5d", "+4d", and commas separating numbers.

3FS -5d
3, 4
5
7
8SS +4d
8, 9

We want to make them like this;

In my demonstration, to isolate the 1st and 2nd predecessors, I introduced several helper columns:

[Pred Delete S F] # to remove F and S

=SUBSTITUTE(SUBSTITUTE(Predecessors@row, "F", ""), "S", "")

[Pred Delete Lag] # to remove the Lag specification like -5d

=IF(FIND(" +", [Pred Delete S F]@row) > 0, LEFT(Predecessors@row, FIND(" +", [Pred Delete S F]@row) - 1), IF(FIND(" -", [Pred Delete S F]@row) > 0, LEFT(Predecessors@row, FIND(" -", [Pred Delete S F]@row) - 1), Predecessors@row))

[Pred 1] # 1st predecessor row number value

=IF(FIND(",", [Pred Delete Lag]@row) > 0, LEFT([Pred Delete Lag]@row, FIND(",", [Pred Delete Lag]@row) - 1), [Pred Delete Lag]@row)

[Pred 2] # 2nd predecessor row number value, if exist

=IF(FIND(",", [Pred Delete Lag]@row) > 0, RIGHT([Pred Delete Lag]@row, LEN([Pred Delete Lag]@row) - FIND(",", [Pred Delete Lag]@row) - 1), "")

If you successfully get the row number (in this case, a still a string value), getting the predecessor's status value is straightforward, using INDEX().

[Pred 1 Status]

=IF(ISBLANK([Pred 1]@row), "", INDEX([Manual Status]:[Manual Status], VALUE([Pred 1]@row)))

[Pred 2 Status]

=IF(ISBLANK([Pred 2]@row), "", INDEX([Manual Status]:[Manual Status], VALUE([Pred 2]@row)))

These values can then be used for conditional formatting as illustrated in the attached image. Please see the link below for the published sheet:

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

I hope this helps in building your formula for flagging individual successors based on the status of their initial deliverables.😀

jmyzk_cloudsmart_jp · 06/05/24

Hi @blawrence13

To do that kind of repetitive work in the formula, it is best to use many helper columns and the same formula pattern, changing only column name numbers and writing the formula systematically.

First, I removed all the S, F, d, +, - from the Predecessors column with this formula;

[Pred Delete SFd+-]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Predecessors@row, "F", ""), "S", ""), "d", ""), "+", ""), "-", "")

Then, find the "," position with the following formula;

[c1] =IF(ISNUMBER([c0]@row), IF(FIND(",", [Pred Delete SFd+-]@row, [c0]@row + 1) > 0, FIND(",", [Pred Delete SFd+-]@row, [c0]@row + 1), ""), "")

[c2] =IF(ISNUMBER([c1]@row), IF(FIND(",", [Pred Delete SFd+-]@row, [c1]@row + 1) > 0, FIND(",", [Pred Delete SFd+-]@row, [c1]@row + 1), ""), "")

[c3] =IF(ISNUMBER([c2]@row), IF(FIND(",", [Pred Delete SFd+-]@row, [c2]@row + 1) > 0, FIND(",", [Pred Delete SFd+-]@row, [c2]@row + 1), ""), "")

[c4] =IF(ISNUMBER([c3]@row), IF(FIND(",", [Pred Delete SFd+-]@row, [c3]@row + 1) > 0, FIND(",", [Pred Delete SFd+-]@row, [c3]@row + 1), ""), "")

You will notice that I am just changing the [cx]'s x from 0 to 3. FYI, [c0]=0.

Then, I get the text or string separated by "," with this formula;

[p1] =IF(ISNUMBER([c1]@row), MID([Pred Delete SFd+-]@row, [c0]@row + 1, [c1]@row - [c0]@row - 1), [Pred Delete SFd+-]@row) # In this case, you just return [Pred Delete SFd+-]@row if there is no ",".

[p2] =IF(ISNUMBER([c2]@row), MID([Pred Delete SFd+-]@row, [c1]@row + 1, [c2]@row - [c1]@row - 1), "")

[p3] =IF(ISNUMBER([c3]@row), MID([Pred Delete SFd+-]@row, [c2]@row + 1, [c3]@row - [c2]@row - 1), "")

[p4] =IF(ISNUMBER([c4]@row), MID([Pred Delete SFd+-]@row, [c3]@row + 1, [c4]@row - [c3]@row - 1), "")

Finally, you can get the Predecessors number string values with the following formula by bringing the left side of " " if there is " "; for example, "3 2" means predecessor 3 and lag of 2 days, but you want the predecessor 3.

[Pred 1] =IF(FIND(" ", [p1]@row) > 0, LEFT([p1]@row, FIND(" ", [p1]@row) - 1), [p1]@row)

[Pred 2] =IF(FIND(" ", [p2]@row) > 0, LEFT([p2]@row, FIND(" ", [p2]@row) - 1), [p2]@row)

[Pred 3] =IF(FIND(" ", [p2]@row) > 0, LEFT([p2]@row, FIND(" ", [p2]@row) - 1), [p2]@row)

[Pred 4] =IF(FIND(" ", [p4]@row) > 0, LEFT([p4]@row, FIND(" ", [p4]@row) - 1), [p4]@row)

With those predecessors, you can get predecessors No 3 and 4's status with the following formula;

[Pred 3 Status] =IF(ISBLANK([pred 3]@row), "", INDEX([Manual Status]:[Manual Status], VALUE([pred 3]@row)))

[Pred 4 Status] =IF(ISBLANK([pred 4]@row), "", INDEX([Manual Status]:[Manual Status], VALUE([pred 4]@row)))

Like many users, you might wish Smartsheet had a built-in "Split" function similar to Excel or Google Sheets. This can feel limiting when working with data that needs separation.

Well, that was a fun two hours with Smartsheet formulas, according to the activity log.😁

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

jmyzk_cloudsmart_jp · 05/30/24

Hi @blawrence13

To effectively track the status of predecessors, it's essential to first retrieve the predecessor's row numbers.

The column for predecessors includes unique formats such as "3FS -5d", "3, 4", "5", "7", "8SS +4d", and "8, 9", featuring characters like "FS", "SS", "-5d", "+4d", and commas separating numbers.

3FS -5d
3, 4
5
7
8SS +4d
8, 9

We want to make them like this;

In my demonstration, to isolate the 1st and 2nd predecessors, I introduced several helper columns:

[Pred Delete S F] # to remove F and S

=SUBSTITUTE(SUBSTITUTE(Predecessors@row, "F", ""), "S", "")

[Pred Delete Lag] # to remove the Lag specification like -5d

=IF(FIND(" +", [Pred Delete S F]@row) > 0, LEFT(Predecessors@row, FIND(" +", [Pred Delete S F]@row) - 1), IF(FIND(" -", [Pred Delete S F]@row) > 0, LEFT(Predecessors@row, FIND(" -", [Pred Delete S F]@row) - 1), Predecessors@row))

[Pred 1] # 1st predecessor row number value

=IF(FIND(",", [Pred Delete Lag]@row) > 0, LEFT([Pred Delete Lag]@row, FIND(",", [Pred Delete Lag]@row) - 1), [Pred Delete Lag]@row)

[Pred 2] # 2nd predecessor row number value, if exist

=IF(FIND(",", [Pred Delete Lag]@row) > 0, RIGHT([Pred Delete Lag]@row, LEN([Pred Delete Lag]@row) - FIND(",", [Pred Delete Lag]@row) - 1), "")

If you successfully get the row number (in this case, a still a string value), getting the predecessor's status value is straightforward, using INDEX().

[Pred 1 Status]

=IF(ISBLANK([Pred 1]@row), "", INDEX([Manual Status]:[Manual Status], VALUE([Pred 1]@row)))

[Pred 2 Status]

=IF(ISBLANK([Pred 2]@row), "", INDEX([Manual Status]:[Manual Status], VALUE([Pred 2]@row)))

These values can then be used for conditional formatting as illustrated in the attached image. Please see the link below for the published sheet:

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

I hope this helps in building your formula for flagging individual successors based on the status of their initial deliverables.😀

blawrence13 · 06/04/24

Hi jmyzk - thank you!! this is incredibly helpful!… the 1 problem I still have (which I apparently didn't include in my initial example) is how to handle tasks that have more than 2 predecessors (so more than just left & right). Any thoughts on how to parse 3-4+ dependencies out and capture them as well?

jmyzk_cloudsmart_jp · 06/05/24