Gary of Muscatine, IL asks: We have multiple schedulers who create their own schedules using a customized template. After the schedules are created, we would like to combine them so we can see the total demand for resources over all schedules and then produce a schedule for each resource. We're also looking for resource leveling over the combined schedules. We tried using an external resource pool, and it doesn't seem to work for us because not all schedules are created with access to the shared resource pool file. Is there a way to combine the schedules and the resources when we're ready to produce the final schedule?
Ellen answers: You can produce the combined schedule you need using any level of Microsoft Project. Each scheduler has his or her schedule, but the benefits of the combined schedules could show overall resource demand when combined to produce a single schedule. Here are the ingredients for doing that.
Resource Consistency
When the schedules are combined, the resource names must be consistent. Exact spelling and spacing will be important. Any deviation in the name will cause Microsoft Project to consider the resource unique, which means they won't be combined. Create a completed Resource Sheet in a file and have all of the users copy and paste the resources into their projects. This copy and paste step will ensure consistency in the resource names.
A Customized Template
Each scheduler should create his or her own schedules using the customized template. When the schedules are combined, the customized fields or data will carry through to the Master file. During schedule creation the schedulers should assign resources to tasks using the resources from the copy-and-pasted Resource Sheet. Communicate to the schedulers about when the schedules should be completed and combined. Some users create a folder on a shared drive and place completed schedules in the folder when ready. This is advantageous for those combining the schedules as they'll be aware of the schedules that are completed and schedules that are still in development.
A Master File
Create a Master file using the same customized template as the schedules when they were created. This ensures use of the customized objects. After creating the Master file, insert the individual schedules into the Master file as read-only without a link:
To accomplish this insertion for each project going into the Master schedule:
1. Click on the task line where you would like the project inserted.
2. For Microsoft Project 2010 click on Project | Subproject.
For earlier versions of Project click on Insert | Project.
3. Then click on the project file to be inserted.
In the lower right corner of the Insert Project box you'll see a checkmark next to Link to Project (Figure1). If the checkmark is on, the resources won't be combined, so uncheck it. Click the Insert button to complete the insertion of the project schedule into the Master Schedule. Repeat for each schedule.
Note! With the checkmark off the inserted project is actually a copy of the schedule and the inserted schedules become part of the Master schedule. Changes made in the Master file or combined schedule won't be updated to the member projects. This could create an out-of-sync situation between Master file and member files if changes are needed.
Figure 1
Once the schedules are combined, filters, groups, and views may be applied to view them. The customized templates in this example contained custom fields and other objects that can be used in the combined schedule. Following are examples of combined reports.
Gantt Chart View
When a project is inserted with a link, an icon will appear in the indicator column that represents an inserted project. In Figure 2 the inserted projects are indicated in yellow (manually noted) and the inserted project icon isn't present in the indicator column. The inserted projects have become part of one combined file. The customized template contained a customized column called "Supervisor," which is viewed across both projects. You will also note the red person icons indicating the tasks that contain over allocated resources. (This is a feature of Project 2010 only.)
Figure 2
Resource Usage View
The Resource Usage view will show the resources allocated over the combined projects. Because the projects are combined, all tasks will appear to be in the Master Project and not the member projects.
Figure 3
Using the customized field "Supervisor" contained in the template, you can apply the autofilter to view the tasks for a specific Supervisor (Figure 4).
Figure 4
Groups may be applied to get information across the inserted projects. Figure 5 shows an example of a grouping by Week by Supervisor across projects.
Figure 5
Team Planner View
Project 2010 Professional has the Team Planner view, which will show the resource assignments in graphic form. Overallocations are shown with red bars on the top and bottom of the blue bars in Figure 6.
Figure 6
You can also level resources in the Master Project to see the total effect for all of the member projects. Applying the autofilter to the Resource Name column in the Task Entry table or the Using Resource filter will result in tasks for specific resource reports.
Tip! If you need to know which task belongs to a member project, consider creating a customized column with the project name before combining the projects. The customized file could be used as a filter or grouping field in the combined schedule. Some people prefer to add letters or numbers at the front of the task name to indicate the source of of the task.
With over 20 years of corporate training experience, Ellen Lehnert, MVP, PMP, MCT, MCITP, is a specialist in Microsoft Project and Microsoft Project Server. She's the co-author of "Managing Projects with Microsoft Project 2010 Desktop," published and available for puchase through MVP-Press. Ellen brings a unique combination of licensed teacher and programming background to the classes she teaches as well as process development, installations, and consulting. Contact her at ellen@lehnertcs.com or at LehnertCS.
Do you have a question for our experts? Send that to editor@mpug.com.