I have seen many posts about people wanting the ability to indicate a particular task/row is ready to begin because all of the predecessors are complete (many of them archived). All of the proposed solutions had short comings (e.g. only works on rows with a single FS predecessor with no lag). Here is my full solution to the problem.
Note 1: I have not tested this extensively. It may not perform well on large sheets.
Note 2: In this example, I have chosen for this to only work with FS predecessors (because that’s what makes sense to me). However, I indicate below which formula to change so that this can work with any predecessor type.
This
solution can be done by adding a minimum of 2 columns to your existing sheet,
but I'm going to layout this explanation using 6 columns for ease of following.
I will provide the 2 column solution at the end.
Here
are the 6 column names I used: Row ID, Row ID Comma, Pred Replace FS, Pred
Count, Pred Done Count, and Ready Status
The green columns can be converted to column formulas. Unfortunately, I don’t know of a way to get a reliable row ID column using a column formula.
Column 1: Row ID
=COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> ""))
In this example, I have referenced the [Task Name] column, but any column can be used. I stole this formula from another post thanks to Paul Newcome. This formula works regardless of how you move, copy, paste rows. The only downside is that it can’t be a column formula. https://community.smartsheet.com/discussion/69476/row-numbers
Column 2: Row ID Comma
=", " + [Row ID]@row + ","
This column is the column that will actually be looked up by the counting formula. The commas are needed to avoid false positive matches (e.g. prevent finding “2” in “20”, looking up “, 2,” won’t be found in “, 20,”
Column 3: Pred Replace FS
=", " + SUBSTITUTE(Predecessors@row, "FS", ",") + ","
This formula adds the needed commas at the beginning and end of Predecessor column, as well as changing “FS” into a comma (needed to ensure a FS predecessor with a lag will still work). Modify this formula with more nested substitute formulas to make it work with other predecessor types (FF, SS, SF) as you desire.
Column 4: Pred Count
=COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, [Pred Replace FS]@row) > 0)))
This formula counts the number of rows where the [Row ID Comma] (looks at every row) is found inside the [Pred Replace FS] column (current row only).
Column 5: Pred Done Count
=COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, [Pred Replace FS]@row) > 0), [% Complete]:[% Complete], 1))
This is similar to the [Pred Count] column, except it adds an additional condition: the % Complete column must also equal 100%. This condition can be changed to point at a different column (e.g. check box column, Status column, etc.).
Column 6: Ready Status
=IF([Pred Done Count]@row = [Pred Count]@row, "Ready", "Waiting")
This column compares the [Pred Count] column and the [Pred Done Count] column. If the counts match, then all the predecessors are finished.
2 Column Solution:
Column 1: One Formula Row ID
=", " + COUNTIFS([Task Name]$1:[Task Name]@row, OR(@cell = "", @cell <> "")) + ","
I combined [Row ID] and [Row ID Comma] into a single formula. This cannot be a column formula.
Column 2: One Formula Ready Status
=IF(COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE(Predecessors@row, "FS", ",") + ",") > 0), [% Complete]:[% Complete], 1)) = COUNT(COLLECT([Row ID Comma]:[Row ID Comma], [Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE(Predecessors@row, "FS", ",") + ",") > 0))), "Ready", "Waiting")
I combined [Pred Replace FS], [Pred Count], [Pred Done Count], and [Ready Status] into a single formula. This can be converted to a column formula.
