Notifying a task owner when predecessor gets completed in Smartsheet

Though Smartsheet tool comes loaded with several useful features and functionality, there are few enhancements that you can make to boost your productivity. This blog focuses on one such enhancement; notifying a task owner when predecessor gets completed.

Why is it needed?

Most of the projects have dependent tasks that follow a sequence and delayed projects may impact project costs, resource availability, customer relationships, and related business needs. This custom enhancement is focused on getting the information flowing automatically while making sure that the sequences start working as soon as the previous task is completed.

How does it work?

Being a project manager, you must be familiar with the fact that predecessor-successor relationship plays a crucial role in project planning. While Smartsheet allows you to connect tasks to predecessors out of the box, there is no way to send out notifications to the owner of successor task when the predecessor gets completed and their task is ready to be worked on.

To overcome this challenge, we have put together a few steps which you can implement in your Smartsheet project plans that will trigger automatic notifications to successors when predecessor gets completed.

Steps to automation

Create new columns

This automation will require 4 additional columns to be created. You can hide these columns in the sheet as these will be used only for calculations and automation purpose. The new columns to be created are:

  • Row ID – this will contain the automatically generated row number.
    • Create a column called Row ID
    • Select Column Type to be “Auto-Number/System”
    • Select System-Generated column to be of “Auto Number”
Insert column
  • Predecessor Copy – this will copy the value from “Predecessor” column. It is not possible to use the value directly from “Predecessor” column and therefore, we need to create this additional column which can be used in calculations. To copy value from Predecessor column, apply below formula to this column
  • Predecessor Value – This column is used to convert the predecessor value from number to text. Apply below formula to this column
    =VALUE([Dependency Value]@row)
  • Predecessor Status – this will check the status of predecessor task and trigger the automation automatically. Apply below formula to this column
    =IF(INDEX(Status:Status, MATCH([Predecessor Value]@row, RowID:RowID, 0)) = “Complete”, “Ready”, “Not Ready”)

Create an automation workflow

Go to Automation and then create a workflow.

Create workflow

Set trigger to “When rows are added or changed”.

Set When “Predecessor Status” changes to: Ready

In the Action box, set Alert someone.

Set it to be sent to contacts in a cell and select “Assigned to” column.

Set a custom email text and select columns to be sent to the receiver.

The set up is now complete. Whenever a predecessor task in the sheet gets completed, the contact in “Assigned To” column of the successor task will get an alert email.

This is custom heading element

We recently had a webinar on Smartsheet basics, so to learn more about it please do registration and the video link will be sent to you.

Related Posts

Comments (3)

Hello! This has been really helpful but I keep hitting an error on the third column. I think the issue is that our predecessors have timing attached to it – so instead of it being “2” it’s “2fs+8h”. I’ve noticed that the formula works when the predecessor is just a number, is there a way to make it work when it has the additional timing? Or is there a different error occurring? Thanks.

I am hitting an error on the third column as well, #NO MATCH. I see you reference a field named “Dependency Value” in the formula for the third column (Predecessor Value), but I don’t see where this comes from as it’s not referenced anywhere in your article. I tried replacing Dependency Value with Predecessor Copy and that’s when I get the error #NO MATCH in the Predecessor Status column. In my situation, the predecessors are just numbers.

I keep getting an error when adding the formula to the third column, something to do with dependency value, can you help?

Leave a comment