Assistance creating formula to reference status of previous task

dmarkson · 08/03/23

My team currently uses Smartsheet to manage the various projects we have across our locations. Within each location is a project line, and then nested within this is the project schedule and then nest within that are the various tasks associated with completing the project. Each task is assigned to a user responsible for the task and start and end dates are input to indicate when work will begin and be completed for the task. The user is responsible for updating the completion date should it be taking longer to complete than anticipated, and then checking a checkbox once they have completed the task.

Each user has a dedicated report that pulls only the tasks that are currently assigned to them to complete to show what they should be actively working on vs. what is upcoming. The issue we are running into is that tasks show up that have predecessors assigned to others to complete, but there's no way of indicating whether the previous task has been flagged as completed, or if it is overdue.

The other part of the challenge is that new projects are constantly being added, or additional tasks get inserted as more information comes to light on what is needed for the project. Because we insert new rows, this then shifts row numbers.

I'm hoping to identify a solution that would allow us to create some kind of flag that a previous task has not yet been completed, but also updates as new rows are added so as not to break the formula.

I've created a formula for our Overdue flag using the following that indicates that the task in that row is overdue, but I'm needing to then flag the following task. This is the formula used there:

=IF(AND([Completion Date]@row <> "", [Completion Date]@row < TODAY(), [Task Complete]@row <> 1), 1, 0)