Project Management: Automating Timelines Across Multiple Projects/Sheets

Robert Cybulski

Before I go into specifics, maybe the simplest form of my question is: can I somehow mimic the functionality of LiquidPlanner's "Smart Schedules" in Smartsheet? (Just to clarify, my company doesn't have LiquidPlanner nor does it plan to, it's just the only product I've heard of that has this functionality out of the box.)

https://www.liquidplanner.com/product/#smart-schedules

For specifics, here are the challenges I face with my projects (which are all in web content development):

• I need to provide approximate timelines for deliverables in each of my projects.
• Each of my projects has multiple deliverables, each deliverable has 20+ tasks (always essentially the same tasks), and I have at least 3 projects running at any one time. Each task occupies its own row under a parent deliverable row in my project sheets. Each task row has its own start and finish date cells.
• Start/finish dates are constantly in flux due to: 1) My work has to be sent to company-internal clients for review and approval multiple times per deliverable, and their feedback is often overdue. 2) Meetings and unscheduled priority work arise and delay scheduled tasks.
• If one task is delayed, it's easy enough to go into its project sheet and update the start/finish dates (using dependencies) for its deliverable, creating a new timeline for that deliverable. However, one problem is that this new timeline doesn't take into account the timelines for the other deliverables on the project (housed in the same sheet). The other problem is that this new timeline doesn't take into account the deliverable timelines for other projects (housed in other sheets).
• I don't have the time to manually go check that updating one deliverable's timeline hasn't caused timing and resource conflicts on other deliverables. Because of this, my approximate timelines get less and less accurate all the time.

I'm open to any suggestions, because I guarantee my view of project management is too limited and there are many solutions other than the kind of functionality LiquidPlanner has. I also have limited experience with spreadsheet formulas in general, and am probably missing some easy automation opportunities.

Isaac Jose

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