Making Multi-Project Graphs from Separate Microsoft Excel Plans for Version 5.3

Making Multi-Project Graphs from Separate Microsoft Excel Plans for Version 5.3 (X53-8_4_1-12212015)

Suppose you have multiple Microsoft Excel plans that are related and you want to create a single project view that will show the important schedule items. You can do this as long as the related Microsoft Excel plans are constructed with unique Task ID column contents.

This is necessary because unlike Microsoft Project, Microsoft Excel does not automatically provide a unique identifier for rows. Additionally, unique Task ID numbers are needed so that OnePager Express does not get rows confused when performing snapshot updates.

Preparation for Making a Multi-Project Graph from Separate Microsoft Excel Plans

1) To illustrate what is needed let’s construct a multi-project project view from the BlueGrass 2J-303U Microsoft Excel file by splitting this file into three distinct files as shown below:

Merlin Project Phase 1 X53-8_4_1-53-(1)-12122015.png Merlin Project Phase 2 X53-8_4_1-53-(2)-12122015.png

2) Each of the Microsoft Excel plans above have unique “Task ID” columns with unique numbers. It is important throughout the use of these Microsoft Excel plans that these assigned unique Task ID numbers remain with their respective tasks. Dates may change in the rows along with other updated information as the projects progress but the Task ID number must remain associated with the task activity. Tasks may be deleted if required but the deleted Task ID should not be reused for a later snapshot. OnePager Express can process out of sequence Task ID numbers.

Adonis Project Phase 1 X53-8_4_1-53-(3)-12122015.png

Setting up the Template for a Multi-Project Graph

3) The next step is to select and update an appropriate OnePager Express template. For this illustration we’ve selected the “Multi-Project Gantt Chart – Detailed” template.

Adjustments to Rows and Swimlanes

4) Next, go to the “Rows/Swimlanes” tab and make sure that the “Group rows into swimlanes” and “Label swimlanes by” windows are set to “Project”. Also, for this illustration, make sure that row labels will be displayed and that the “Label rows by” window is set to “Phase”. The “Rows/Swimlanes” tab will look like this:

X53-8_4_1-53-(6)-12122015.png

5) When the steps above are completed and the template updated click the “Save and use” button to make the modified template the current template.

Create a Multi-Project Graph from Separate Multiple Microsoft Excel Plans

For this example we will use the OnePager Express Add-in application.

6) Launch Microsoft Excel and open the first of the multi-project Microsoft Excel plans. In this case it’s the “Merlin-Project-Phase-1” file. Then, launch OnePager Express from the Microsoft Excel “Add-in” tab. The “OnePager Express Start” form will appear. Click the “NEW” button which will bring up the “OnePager choices” form as shown below:

X53-8_4_1-53-(6A)-12122015.png

7) Note that the “Merlin-Project-Phase-1.xlsx” Microsoft Excel source file appears in the “Selected File(s)” section and that the “Add/Remove” button is active. Now, click the “Add/Remove” button which will bring up the “Data source selection” form as shown below:

X53-8_4_1-53-(6A1)-12152015.png

8) Now we want to add the other two Microsoft Excel source files to the source packet. To do this, click the “ADD” button in the “Data source selection” form and a Windows “Open” form will appear. Navigate to the desired source files, select them individually, and the click the “Open” button as shown below:

File:X53-8 4 1-53-(6A2)-12152015.png X53-8_4_1-53-(6A2)-12152015.png

9) After you’ve completed placing the third Microsoft Excel source file into the source packet, the window in the “Data source selection” form will look like this:

File:X53-8 4 1-53-(6A3)-12152015.png X53-8_4_1-53-(6A3)-12152015.png

10) When you click the “OK” button above, the “OnePager choices” form will be updated to reflect that three (3) files are in the Source Packet and the Title for the new project view that we typed in as shown here:

X53-8_4_1-53-(6A4)-12152015.png

11) When you click the “Next>” button, OnePager Express will take you to the second page of the “OnePager choices” form where you can confirm other settings or make template setting changes. When these confirmations are complete, click the “Create new project view” button. The project view will look like this:

X53-8_4_1-53-(6F)-12122015.png

12) Once you merge all your projects into a single snapshot, you can now add a new snapshot to the project view to show how tasks changed as the projects progresses.

Updating a Project View Created from Multiple Microsoft Excel Spreadsheet

13) Eventually you will want to update your multi-project project view by either adding a snapshot or replacing a snapshot with better data. To perform either the ADD or the REPLACE snapshot, the processes described in the related links below can be used.

14) The key to making updated to multi-project project views from multiple Microsoft spreadsheets is the proper management of the Microsoft Excel data in the source packet. Recall that the Source Packet contains the file or files that are to be used to either create or update a project view, they are stored as file names and path names in the project view’s .TAM file, and can be accessed from the “Data” tab’s “Selected file(s)” button on the OnePager ribbon as shown below:

File:X53-8 4 1-53-(6A5)-12152015.png X53-8_4_1-53-(6A5)-12152015.png

15) Validating the contents of the source packet and assuring that these are the Microsoft Excel source files that were updated is an essential step before proceeding to perform a project update.

Related Links

Creating a NEW Project View via Pull Operation - OnePager Express for Version 5.3 7.13.2-53

REPLACING a Snapshot via Pull Operation - OnePager Express for Version 5.3 7.14.2-53

ADDING a Snapshot via Pull Operation - OnePager Express for Version 5.3 7.15.2-53

(8.4.2-53)