Update Status of Dependent task to Go Ahead if all Predecessor Tasks are in Done Status

Venk

Hi,

Using Automation and helper columns, is it possible to update the Status of a Dependent task to Go Ahead if all of its Predecessor Tasks are in Done Status?

Thanks,

Venkat

Brian_Richardson

Hey Brad, good catch. It's because the CONTAINS function is finding "3" in the list of Successors when it contains 3, and also contains 13, and 30, etc etc.

We can address that by changing a couple of things:

1 - Change the Successors column to a Dropdown List and turn on the "Allow multiple values per cell"

2 - Update the Successors column formula to =JOIN(SUCCESSORS(Predecessors@row), CHAR(10))

These steps should give you a column of numbers where the numbers are not listed with commas, but instead show a grey box around each value. This way we have separated the numeric values from each other.

3 - Update the Go Ahead column formula to

=IF(COUNT(COLLECT(Successors:Successors, Status:Status, "Complete", Successors:Successors, HAS(@cell, [Row Number]@row))) = COUNT(COLLECT(Successors:Successors, Successors:Successors, HAS(@cell, [Row Number]@row))), true)

This changes from COLLECT to HAS. HAS is specifically designed to work with multiple selection dropdown columns. The HAS function looks at the list of successors and matches the full value. In other words, if you are matching row 3 to the Successors column, it will only find 3 when using HAS instead of finding 3,13,30,etc

Note that, for some reason, HAS and COLLECT have the arguments reversed from each other, so be sure to switch around the @cell and [Row Number]@row arguments when you replace COLLECT with HAS.

Brian_Richardson

Yes, it's a little complicated but yes.

1. Setup an AutoNumber column called Auto. (or if you have one already, that works too).
2. Setup a Row Number column with the following column formula: =MATCH(Auto@row,Auto:Auto,0)
3. Setup a Successors column with the following column formula: =JOIN(SUCCESSORS(Predecessors@row),",")
4. Setup a Go Ahead column as a checkbox column with the following column formula: =IF(COUNT(COLLECT(Status:Status,Status:Status,"Done",Successors:Successors,CONTAINS([Row Number]@row,@cell))) = COUNT(COLLECT(Status:Status,Successors:Successors,CONTAINS([Row Number]@row,@cell))), true)
5. When the Go Ahead checkbox becomes checked, you can run an automation to set your Status to "Go Ahead" for that task.

The way this works is:

• The first COUNT/COLLECT formula counts up a collected list of all the row Statuses that are marked "Done" that have the current task as one of their Successors.
• The second COUNT/COLLECT counts up a collected list of all the row Statuses of any value, when it has the current task as one of the Successors.
• The formula then compares the two counts. If they are equal, then you know that all Predecessors are marked as Done
• The automation then marks your Status for you. You cannot use a formula in the Status column that refers to the Go Ahead column, or you'll get a formula loop.

BradM

@Brian_Richardson

Brian,

I have the Auto Number Column, Row Number Column and Successors Column setup per your instructions above, in my case, the Status = "Complete":

Auto = Auto number

Row Number =MATCH(Auto@row, Auto:Auto, 0)

Successors =JOIN(SUCCESSORS(Predecessors@row), ",")

I setup Go Ahead per your instructions but all the checkboxes are checked. I tried changing the status to "Complete"to see if that changes anything but, nope. Save, browser refresh, nothing.

Here is what I have:

=IF(COUNT(COLLECT(Status:Status, Status:Status, "Complete", Successors:Successors, CONTAINS([Row Number]@row, @cell))) = COUNT(COLLECT(Status:Status, Successors:Successors, CONTAINS([Row Number]@row, @cell))), true)

Thoughts?

-Brad

Brian_Richardson

Sorry Brad, I told you wrong on that final formula. You need to collect the Successors, not the Status. So:

=IF(COUNT(COLLECT(Successors:Successors, Status:Status, "Complete", Successors:Successors, CONTAINS([Row Number]@row, @cell))) = COUNT(COLLECT(Successors:Successors, Successors:Successors, CONTAINS([Row Number]@row, @cell))), true)

BradM

@Brian_Richardson , I'm getting an odd behavior. If you refer to the image below, row 7 is the starting point of the schedule and it is not checked as being ready. Moreover, row 18 shows a checkmark since it has no Predecessors or Successors but row 15 which has is the same, is not checked. I saved and refreshed the sheet and that did not help.

When I "Complete" row 7, none of the Predecessor rows change.

Thoughts? I am using your formula exactly as you have it.

=IF(COUNT(COLLECT(Successors:Successors, Status:Status, "Complete", Successors:Successors, CONTAINS([Row Number]@row, @cell))) = COUNT(COLLECT(Successors:Successors, Successors:Successors, CONTAINS([Row Number]@row, @cell))), true)

-Brad

BradM

I think the problem is in the CONTAINS. In my sheet, there are 145 rows of data in the schedule. When I created column with formulas to report the first half with a join and a second column with other half, I see odd results:

first =JOIN(COLLECT(Successors:Successors, Status:Status, "Complete", Successors:Successors, CONTAINS([Row Number]@row, @cell)))

second =JOIN(COLLECT(Successors:Successors, Successors:Successors, CONTAINS([Row Number]@row, @cell)))

wouldn't contains find instances where the Row Number is a portion of another? For example, 15 is contained within the string 115?

I really need to unravel Smartsheet formulas more than I have to date.

-Brad

Brian_Richardson

Hey Brad, good catch. It's because the CONTAINS function is finding "3" in the list of Successors when it contains 3, and also contains 13, and 30, etc etc.

We can address that by changing a couple of things:

1 - Change the Successors column to a Dropdown List and turn on the "Allow multiple values per cell"

2 - Update the Successors column formula to =JOIN(SUCCESSORS(Predecessors@row), CHAR(10))

These steps should give you a column of numbers where the numbers are not listed with commas, but instead show a grey box around each value. This way we have separated the numeric values from each other.

3 - Update the Go Ahead column formula to

=IF(COUNT(COLLECT(Successors:Successors, Status:Status, "Complete", Successors:Successors, HAS(@cell, [Row Number]@row))) = COUNT(COLLECT(Successors:Successors, Successors:Successors, HAS(@cell, [Row Number]@row))), true)

This changes from COLLECT to HAS. HAS is specifically designed to work with multiple selection dropdown columns. The HAS function looks at the list of successors and matches the full value. In other words, if you are matching row 3 to the Successors column, it will only find 3 when using HAS instead of finding 3,13,30,etc

Note that, for some reason, HAS and COLLECT have the arguments reversed from each other, so be sure to switch around the @cell and [Row Number]@row arguments when you replace COLLECT with HAS.

BradM

That did it. I saw this post

SUCCESSORS Testing — Smartsheet Community

That used the same Successor's column with the multi select drop down and the HAS statement. I didn't really understand the whole picture but I'm learning.

Thank you for your time in solving this problem.

-Brad

BradM

@Brian_Richardson I might have spoken too soon. It appears this solution does not take into account when a row has 2 Predecessors. It does work for one Predecessor at the moment.

Have any ideas?

Brian_Richardson

It handles any number of predecessors. The formula is counting up the number of rows that show the current row as a successor, and comparing it to a count of rows that show the current row as successor + are in completed status. If the two counts are equal, the box gets checked. It handles unlimited number of predecessors.

I just tested it again in a test sheet and it is working with multiple predecessors in my sheet.

Maybe check the formulas?

In the screen below I split out the Count of Predecessors and the Count of Completed Predecessors to show the two halves of the Go Ahead formula working.

Your formulas should be:

• Auto: (no formula, just an autonumber row)
• Row Number: =MATCH(Auto@row, Auto:Auto, 0)
• Predecessors: (no formula but make sure that Dependencies are enabled in your settings)
• Successors: =JOIN(SUCCESSORS(Predecessors@row), CHAR(10))
• Go Ahead: =IF(COUNT(COLLECT(Successors:Successors, Status:Status, "Complete", Successors:Successors, HAS(@cell, [Row Number]@row))) = COUNT(COLLECT(Successors:Successors, Successors:Successors, HAS(@cell, [Row Number]@row))), true)

If you want to add the two halves of the Go Ahead formula for testing purposes/troubleshooting, they are:

• Count of Predecessors: =COUNT(COLLECT(Successors:Successors, Successors:Successors, HAS(@cell, [Row Number]@row)))
• Count of Completed Predecessors: =COUNT(COLLECT(Successors:Successors, Status:Status, "Complete", Successors:Successors, HAS(@cell, [Row Number]@row)))

BradM

I was looking too deep. Just 10 minutes ago I saw it was COUNT, not a list of the same values.

To test the output, I created 2 columns, one for the first part of the IF statement (not including the count) and the other for the second part. The list of values were not the exact same which kept throwing me. I expected both sides to have the same list, not the COUNT. So, what I was seeing was confusing me to no end.

I had a row that didn't have the formula in it, causing more confusion.

All is good now. Thanks!

Brian_Richardson

Ah, yes I should have mentioned - be sure to right click those formulas and select Convert to Column Formula. Column formulas are one of the best things about Smartsheet - no more wondering if you copied the formula down correctly.