Formula To Indicate A Task Is Ready Because Predecessors Are Complete [Solution]

Tanner PolzinTanner Polzin ✭✭✭✭✭
edited 07/26/21 in Best Practice
02/02/21 Edited 07/26/21

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([email protected], "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([email protected], "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([email protected], "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.

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 02/02/21

    Hi @tpolz

    Nicely done! 👍️

    Thanks for sharing!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Tanner PolzinTanner Polzin ✭✭✭✭✭

    After my initial post, I realized 2 things. First, the formula in my 2 column solution was still referring to old 6 column solution for the Row ID. Second, my formulas could be shortened by replacing each of my nested COUNT(COLLECT( formulas with a single COUNTIFS( formula. Here are all the formulas updated.


    6 Column Solution Update:

    Column 4: Pred Count

    =COUNTIFS([Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, [Pred Replace FS]@row) > 0))

    Column 5: Pred Done Count

    =COUNTIFS([Row ID Comma]:[Row ID Comma], AND(NOT(ISBLANK(@cell)), FIND(@cell, [Pred Replace FS]@row) > 0), [% Complete]:[% Complete], 1)


    2 Column Solution Update:

    Column 2: One Formula Ready Status

    =IF(COUNTIFS([One Formula Row ID]:[One Formula Row ID], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE([email protected], "FS", ",") + ",") > 0), [% Complete]:[% Complete], 1) = COUNTIFS([One Formula Row ID]:[One Formula Row ID], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE([email protected], "FS", ",") + ",") > 0)), "Ready", "Waiting")



    Parent Row Bonus:

    Lastly, I noticed that these formulas "work" on parent rows, but not the way someone would "want" them to work. I would expect that the parent row would only say "ready" if at least one of the children rows says ready. This can be easily accomplished by adding an additional IF statement to the final output formula that counts the number of children that say “Ready.” Full final formula for both solutions below.

    6 Column Solution, Column “Ready Status”:

    =IF(COUNT(CHILDREN()) = 0, IF([Pred Done Count]@row = [Pred Count]@row, "Ready", "Waiting"), IF(COUNTIF(CHILDREN(), @cell = "Ready") > 0, "Ready", "Waiting"))

    2 Column Solution, Column “One Formula Ready Status”:

    =IF(COUNT(CHILDREN()) = 0, IF(COUNTIFS([One Formula Row ID]:[One Formula Row ID], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE([email protected], "FS", ",") + ",") > 0), [% Complete]:[% Complete], 1) = COUNTIFS([One Formula Row ID]:[One Formula Row ID], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE([email protected], "FS", ",") + ",") > 0)), "Ready", "Waiting"), IF(COUNTIF(CHILDREN(), @cell = "Ready") > 0, "Ready", "Waiting"))

  • Tanner -- this is amazing. Thank you for posting your solution!

    I wish SmartSheet offered this inherently, as many schedule management tools like Asana include this feature. It allows the owner of each task to know if they can start on their work.

    I have just implemented this in a complex work plan I am managing and will report back here with any observations and findings.

    Thanks again to Tanner for figuring this out and sharing your method!!

  • Tanner PolzinTanner Polzin ✭✭✭✭✭

    @SJ Sellers You're very welcome. After 2+ years of slowing gaining knowledge about what Smartsheet formulas can do, all of a sudden I had strong need for this on a project. Then it took me a couple of days to put all the pieces together. I was hoping this post would help others with the same need.

  • @Tanner Polzin ... Awesome solution! Although i'm having trouble adapting this formula.

    This works well when dealing with standard predecessors. However when working with anything else it is considering those "predecessors" as needing to be done before it changes to Ready, when actually they are only for timing and not real predecessors. Does that make sense?

    For example, I have my standard work forward schedule for majority of tasks in development. Howver some tasks related to sales or marketing are work backwards tasks for timing, for example we dont need a PO until we are almost ready to release production, and can not start selling until a month before PO is needed. We can not do this task too early, so its a work backwards from production release for this chain of tasks.

    Is there a way to adjust the Pred Replace FS formula to ignore non-predecessors?

    Appreciate if you can help on this?

  • Tanner PolzinTanner Polzin ✭✭✭✭✭

    @Matt Stewart

    Correct me if I'm wrong, but it sounds like you have Start-to-Finish predecessors? You are correct that my formula ignores those type of predecessors.

    I believe that what you really want is to set the status to "Ready" when todays date is ahead of the planned start date (based on the predecessor)? If so, you shouldn't edit the Pred Replace FS. Instead you should edit the Ready Status. You would just need one additional IF statement at the beginning (IF the Pred Count is 0, THEN determine Ready/Waiting based on Start Date, ELSE do what the formula was already doing.

    Here is my suggestion. The italicize part is the IF statement I added at the beginning (plus one a parentheses at the very end to close out the new IF statement.

    =IF([Pred Count]@row = 0, IF(NETDAYS([email protected], TODAY()) > 0, "Ready", "Waiting"), IF(COUNT(CHILDREN()) = 0, IF([Pred Done Count]@row = [Pred Count]@row, "Ready", "Waiting"), IF(COUNTIF(CHILDREN(), @cell = "Ready") > 0, "Ready", "Waiting")))

Sign In or Register to comment.