Ask the Experts: Combining Resources across Projects

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

Ask the Experts: Combining Resources across Projects

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

Ask the Experts: Combining Resources across Projects

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

Ask the Experts: Combining Resources across Projects

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

Ask the Experts: Combining Resources across Projects

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

Ask the Experts: Combining Resources across Projects

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

Ask the Experts: Combining Resources across Projects

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.

Next Webinar

The Art and Science of Estimating Task Lengths

Written by Ellen Lehnert
Ellen Lehnert, PMP, Microsoft Project MVP, MCP, is a independent consultant and trainer on Microsoft Project and Project Server. She has taught Microsoft Project over 400 times and is the author of  MS Project 2010 and 2013 published courseware. Ellen is also a contributor and tech editor for many reference books, a developer for the Microsoft Project certification tests and is a frequent meeting speaker for Microsoft, MPUG and PMI. Contact Ellen at ellen@lehnertcs.com.
Share This Post
Have your say!
00
3 Comments
  1. Differentiate non paid vs paid work

    Hi Ellen.
    I work in a small company that have many small projects. WWe are approximately 10 project managers who run the projects and we use one project file each to manage our projects. The resources are organised in a resource pool and we go over the resource need weekly.

    We also do alot of development work. This work is managed by the project managers, but we would like to have the possibility to differentiate in the hours of our resources in between paid work for externals and internal research (which is just a cost)

    Is there anything we can add (tag filter or similar) to the projects to mark them as non paid work, and have those hours show as a seperate sum in the resource usage?

  2. I have several clients who have created a customized task field to flag if the task represents external or internal work. You would then be able to group and filter on this field when creating your costing reports.

  3. To get what you are looking for:

    Have each project use the same resource names.
    Combine projects without a link Project –> Subproject –> clear the link project box
    Insert projects one at a time
    when all projects are inserted, use the Start column in a task table to group by week. Filter for uncompleted tasks only.
    You will also see the assignments on the Resource Usage View.

    However – you will not see which project the tasks are coming from. To see this you must create a customized short project name field and populate with the project name in each project. Have each project use the same field. Copy the field into your local Global.MPT. the project name then can be inserted in any task table.

Leave a Reply