Project Management: Automating Timelines Across Multiple Projects/Sheets

Hi Robert,

Thanks for your post! It sounds like you're running into some roadblocks when it comes to managing task dependencies and resources across deliverables and sheets as your projects progress and dates need to be updated. I do have a few suggestions that I hope will be helpful to you:

Track Baseline Schedule Variance

Consider implementing a baseline schedule to track date variance as the project progresses. To do this

1. Add two Date columns, Planned Start and Planned End
2. Add one Text/Number column, Variance. In this column, enter the formula =NETWORKDAY([Actual End]@row, [Planned End]@row) - NETWORKDAY Help Article
3. When you spin up a project and enter the dates for the Actual Start and Actual End of each task, copy and paste these values into the Planned Start and Planned End fields, then hide the columns if desired.

At the start of your project, the Variance column should say 0 for every row. However, as tasks are completed late or early, you'll begin to get a numeric indicator of how late or early any given task was or will be compared to the baseline schedule. This will help adjust the duration and prioritize tasks more effectively to ensure that your overall plan stays on track.

Use the Resource Management features

You mentioned that your sheets don't take resource conflicts into account as the schedule is updated. To help address this, consider using the Resource Management features. With Resource Management and Allocation, you can Assign People to a Task and set the Allocation % (the percent of each day that the task will take from its assigned resources for its full duration) of a given task. Although Resource Management does not automatically modify task duration, dates, or assignments, it does make it easier to track allocation and ensure that there aren't any resource conflicts.

It can sometimes be a little bit difficult to visualize the labor or effort of a given task as an Allocation percentage through the duration of the task, so I've also come up with a method that converts "Hours of Effort" required to complete a task into an Allocation percentage based on the duration of that task:

1. Add a Text/Number column, Hours of Effort. This is the number of hours or amount of labor that you think a task will take.
2. Add a Text/Number column, Estimated Allocation, and format it as a percentage.
3. In the Estimated Allocation column, enter the following formula

• =([Hours of Effort]@row / (Duration@row * 8))
• The 8 in the above formula refers to the number of hours in a workday. You can adjust this as needed.

The above formula divides the Hours of Effort (labor hours) by the total working hours (duration in workdays multiplied by 8 hours per workday) to return an Estimated Allocation % that can then be copied into the main Allocation % column or adjusted in the column as needed.

Use Dependencies Across Deliverables

You mentioned that adjusting tasks and deliverable timelines is fairly easy using the dependencies features, yet conflicts with tasks in other deliverables are not taken into account. With Dependencies and Predecessors, you can configure dependencies across deliverables the same as you would within a given deliverable. For example, if Deliverable B can't start until Deliverable A is completed, then you'd want to make the first task in Deliverable B dependent upon the completion of the last task in Deliverable A. NOTE: You'll want to make sure that you always define predecessors in the lowest level child rows because entering a predecessor into a summary row or parent row will apply that dependency to all of that parent rows children (tasks/sub tasks).

Cross Sheet Dependencies

You mentioned that you also struggle with adjusted timelines across several sheets. For the resource management piece of this issue, I'm happy to announce that the Resource Management features do take allocation across project sheets into account when calculating total resource allocation.

For non-resource-related roadblocks for tasks which depend upon the start or completion of tasks in other project sheets, there currently isn't a feature which can automatically define dependencies across sheets. When you have a moment, please submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet.

If your project sheets are all shared with the same people and aren't incredibly large, you could consider tracking all active projects under their own hierarchical parent rows within the same sheet. If all your active projects are in one sheet, you can set up dependencies for tasks across projects. For your reference, current sheet size limits can be found here (Insert or Delete Rows or Tasks) and below:

• 200 columns
• 5000 rows
• 200,000 rows (>40 columns means <5000 rows will be available)

If you want to keep your projects in separate sheets, you can mimic cross-sheet dependencies with Cell Links. Using Cell Links, you can create a row in Project Sheet A and link in task data from a row in Project Sheet B. Then, in Project Sheet A, you can make tasks dependent upon the cell linked row. As the dates are updated in Project Sheet B, the linked row will also update in Project Sheet A, and dependencies will adjust other rows automatically.

I hope the above has been helpful!

Isaac Jose

Smartsheet Support