Project Schedule 'Mash-ups' to create Master Program Schedule?

Dom Orsler

Hi

I'd like to be able to link five project schedules within a program and as I see things, this can be done two ways; 1) five separate schedules, all of which use a set of common program milestones and task dependencies linked between project schedules, or 2) mashing all of the project schedules together into one sheet, but being able to view separate project schedules through the application of filters using (e.g.) a column named 'project'.

Has anyone else tried this? I appreciate there may be more to the second option than meets the eye and would prefer to benefit from the wisdom and experience of others before I waste a week messing around.

Thanks

Dom

BullandKhmer

You cant make a task on one sheet dependent on a task on another sheet. So there goes option one.

Option 2 is possible, I'd suggest using reports rather than filters to display specific projects.

Cheers.

Dom Orsler

So you can't establish an inbound or outbound dependency between tasks in separate work sheets?...! One of the core, critical capabilities of program management (or even just managing more than one related project, which isn't necessarily program management) is managing this, so are you saying SmartSheets can't be used effectively in program management?...!

Cheers.

DanR

HI Dom. I built a system similar to what you're describing some years ago. Our fabrication facility has to manage the schedules of 30 or more projects through 9 fabrication departments. All the project schedules are on one Smartsheet. The primary column (In my example it's named Task Name) is used for the project name, then all the departmental tasks are indented below it.

The easiest way to view each schedule is to expand or contract the schedules using the +/- sign in the header line. If you want to use filters do the following:

First create a hidden helper column named Level to indicate the indentation level of the line using a formula like this: =COUNT(ANCESTORS([Task Name]@row)). Create another hidden helper column and name it something like Project Name. Enter a column formula like this: =IF(Level@row < 1, "", INDEX(ANCESTORS([Task Name]@row), 1)). This will display the project name that is entered in your project header line in each cell of the column below the header line. Use this Project Name column in your filter criteria. Make sure to select "Include Parent Lines" in your filter to show the project header line. You can also use the Project Name column to create reports, but reports won't display the parent/child relationships of your task lines, which can make things confusing.

I do use reports to help prioritize the tasks for each fabrication department. The reports display only the tasks for a particular department, but for all the 30 or more projects on the schedule. They're displayed in order of their due date, and I use the Project Name helper column to identify which project each task is associated with since I don't have parent lines.

I hope that make sense. Good luck.

Dom Orsler

Many thanks for that valuable example, Dan