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

Tanner Polzin

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.

Andrée Starå

Hi @tpolz

Nicely done! 👍️

Thanks for sharing!

Be safe and have a fantastic week!

Best,

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

Tanner 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(Predecessors@row, "FS", ",") + ",") > 0), [% Complete]:[% Complete], 1) = COUNTIFS([One Formula Row ID]:[One Formula Row ID], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE(Predecessors@row, "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(Predecessors@row, "FS", ",") + ",") > 0), [% Complete]:[% Complete], 1) = COUNTIFS([One Formula Row ID]:[One Formula Row ID], AND(NOT(ISBLANK(@cell)), FIND(@cell, ", " + SUBSTITUTE(Predecessors@row, "FS", ",") + ",") > 0)), "Ready", "Waiting"), IF(COUNTIF(CHILDREN(), @cell = "Ready") > 0, "Ready", "Waiting"))

Tanner Polzin

Thanks to @Sterling Crawford, I realized I can update my row number formula and make it a column formula. https://community.smartsheet.com/discussion/69476/row-numbers

For my solution it does require one more column. You'll need a column where every value is unique. The easiest way to do that is to add an auto number column that just starts at 1 and goes up. I named my column "Auto Number".

With that column added, here is the updated formulas.

6 Column Solution (now 7 columns):

Column 1: Row ID

=MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0)

2 Column Solution (now 3 columns):

Column 1: One Formula Row ID

=", " + MATCH([Auto Number]@row, [Auto Number]:[Auto Number], 0) + ","

SJ Sellers

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 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.

Matt Stewart

@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 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(Start@row, 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")))

Neil Watson

@Tanner Polzin I am using your method and it works great for FS, however as you point out above the formula needs modification for FF, SS & SF. You mention using more nested substitute formulas to make it work with other predecessor types but I am not able to get that to work. Could you elaborate further on this please?

Tanner Polzin

@Neil Watson I'm sorry, I won't be able to help you with this due to my current workload. Also, I find it hard to come up with a general solution for the other predecessor types (FF, SS, & SF).

For the normal FS predecessor, it is a fairly straight forward on how everyone would want it to operate. The successor only becomes "ready" when the predecessor is "finished."

However, for the other predecessor types, I think different people would want different results. Take SF for example. Say Task2 is the successor to Task1, so in Task2, the predecessor column says 1SF. This means that Task2 should finish before Task1, so when is Task2 ready to start? The predecessor columns can point me to Task1, but nothing about Task1 tells me when Task2 is ready to start. You could make this auto change to "ready" when today's date is passed the planned start date for Task2 (which I gave the solution to in the previous post). However, the point of the "ready" column is to say it is ready to work on regardless of date, so I assume other people would want it to be different.

You'll need to very clearly define what you want to happen for each of the other predecessor types. After you do that, I would suggest that you implement my current 7 column solution. Then try to modify each column individually before combining back into 3 columns (and you might need to add even more columns).

Neil Watson

@Tanner Polzin thanks for your insights. Since we predominantly use only one other variant (SS), I will use your multiple column method and get that to work for FS and SS.

malden

This worked for me and is going to be a huge help for my team. Thank you!

malden

This worked for me and is going to be a huge help for my team. Thank you!

Tanner Polzin

You're welcome. I'm glad others are still getting use out of it. I personally have about 20 different schedules that use this. It would be a huge pain without it.

Harley Esguerra

@Tanner Polzin - this was super amazing! @Neil Watson - I also use the different relationships (SF, SS) in my plans. Interested to hear how you worked around this?

Tanner Polzin

@Harley Esguerra Glad to hear this is still helping people. I'm still actively using it on 30 different schedules.

I haven't made any updates to the "Ready" formula. However, thanks to some other posts (I can't remember or I'd give some credit), I found a way to create new columns that list all predecessors and successors. You could then potentially use these to help you modify the "Ready" formula.

If I ever modify my ready formula I'll post the update. However, I still don't know how to treat the other predecessor types. Take an example where Row2 has a SS Predecessor of Row1. The successor and the predecessor in this case are actually supposed to start at the same time. So when should Row2 say "Ready?" If we wait until Row1 is done, then Row2 would say "Ready" too late. We could wait until Row1's status changes to something like "In progress," but that almost never happens for my schedules, and if they truly are parallel tasks, then I want both of them to say "Ready" regardless of Row1's status. At least for the SS type, I just eliminate them from my schedules. For this specific example, I just copy Row1's Predecessor (e.g. 3, 5FS +10d, 7) and paste that same value into Row2's Predecessor column. Now both rows will say "Ready" at the same time when rows 3, 5, and 7 are complete (as I would want a SS to operate). There might be a way to change my formula so Row2's Ready Status always mimics Row1's Ready Status, but modifying the formula for that seems harder than just getting rid of the SS type.

This problem is even worse for SF and FF types. I mention more about this in my previous post.

Enough rambling. Let's move on to the new columns that may potentially help you.

Successor Rows:

Successor rows are actually easier because of the SUCCESSOR formula already built into Smartsheet. Make sure you add a new column, and make that column a drop down list that allows multiple cell values per cell. This formula assumes your main task column is called "Task Name."

=JOIN(SUCCESSORS([Task Name]@row), CHAR(10))

Predecessor Rows:

This will require two columns. Both columns need to be formatted as dropdown lists with allowing multiple values per cell (see screenshot above). Both columns can be column formulas.

First column: Predecessor Fuzzy Parse - This column assumes your predecessor columns is called "Predecessors." This column strips all of the Fs, Ss, and commas out of the predecessor column text. The text in between each of these values is then stored as separate values. Each value left should be a row number or a lag (e.g. +30d). Example screenshot output below.

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

Second column (actual desired output): Predecessor Rows - This column assumes your already have my Row Number column from my previous post, and you already created the Predecessor Fuzzy Parse column above.

=JOIN(COLLECT([Row Number]:[Row Number], [Row Number]:[Row Number], HAS([Predecessor Fuzzy Parse]@row, @cell)), CHAR(10))