Difference between revisions of "Making Multi-Project Graphs from Separate Microsoft Excel Plans for Version 6.0"

Revision as of 19:08, 8 February 2017

Overview

Suppose you have multiple Microsoft Excel source 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 source 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 (OPX) 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 source plan by splitting this file into three distinct files as shown below:

Important Note

2) Each of the Microsoft Excel source plans above have unique Task ID columns with unique numbers. It is important throughout the use of these Microsoft Excel source 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 but the deleted Task ID should not be reused for a later snapshot. OPX can process out of sequence Task ID numbers.

Setting up the Template for a Multi-Project Graph

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

Adjustments to Rows and Swimlanes

4) Once the OPX' current template is established as a multiple-project template, 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 text columns will be displayed and that the Custom Text Columns are set to Phase. The Rows/Swimlanes tab will look like this:

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

6) For this example we will use the OPX Add-in application. Launch Microsoft Excel and open the first of the multi-project Microsoft Excel source plans. In this case it’s the Merlin-Project-Phase-1 file. Then, launch OPX 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 (OPC) form as shown below:

7) Note that the Merlin-Project-Phase-1.xlsx Microsoft Excel source plan appears in the Selected File(s) group and that the Add/Remove button is active. Click the Add/Remove button which will bring up the Data source selection form as shown below:

8) We want to now add the other two Microsoft Excel source plans 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 plans, select them individually, and then click the Open button as shown below:

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

10) When you click the OK button above, the OPC 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:

11) When you click the Next> button, OPX will take you to the second page of the OPC 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:

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.

Updating a Project View Created from Multiple Microsoft Excel Plan

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, use the processes described in the related links below.

14) The key to making updated to multi-project project views from multiple Microsoft source plan 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 ribbon as shown below:

15) Validating the contents of the source packet and assuring that these are the Microsoft Excel source plans 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

REPLACING a Snapshot via Pull Operation - OnePager Express

ADDING a Snapshot via Pull Operation - OnePager Express

(8.4.1-60)