Tommy's Tips - Dynamically Linking Microsoft Project Data ...



Tommy's Tips - Dynamically Linking Microsoft Project Data to an Active Excel Graph

 

This article is a bit different, but one day you might need to plot a graph using project data from Microsoft Project 2000, so it might just come in handy!

My client needed to plot certain information, such as Percent Complete vs. Duration, for a report to his superiors. The data was from three different projects in as many locations within the USA. The data needed to be compiled into a Master Schedule, dynamically linked to Microsoft Excel, then linked again to a Bar Chart on a separate worksheet which displayed the different tasks and their Percent Complete versus their individual Durations, and other data. The graph had to be Read Only and remain open all day so upper management could view the constantly changing data. This is how we did it!

Converting Microsoft Project 2000 Data for Analysis in Microsoft Excel

 

Any data can be linked from Microsoft Project 2000 to Microsoft Excel or Microsoft Word, etc. By now, after many Tommy's Tips, you should be pretty handy with dynamic linking.

When converting Microsoft Project 2000 data, it is highly recommended that dynamically linked files be kept in the same folder. BUT…What if you need to analyze certain data, such as Duration, in Microsoft Excel that is formatted "10 d", meaning 10 Days Duration? Excel won't do much with "10 d" in a graph. How do we isolate the "10" and "d" so Excel will read and analyze just the integer 10? What about Percent Complete? What if Excel won't analyze anything but the integer?

 

So many questions, and as usual it's NEVER easy! However, there is a method in Excel by which the two can be separated. Go to Excel, and Data Text to Columns; this will do OK if you want to go through the motions. However, there is a much better method; you can isolate the integers in Microsoft Project 2000 before sending it to Excel.

 

Any string of characters in Microsoft Project 2000 can be converted to an integer then changed mathematically back to the desired integer before sending it to be used in an Excel graph. There is a function in Microsoft Project 2000 entitled CInt(expression), which means "Convert this expression to an Integer", that will convert the entire character string to an integer.

Here’s how we use this function: Insert two columns, Text1 and Text2 adjacent to Duration and %Complete, respectively. Starting with %Complete, right-click on the Text1 heading and select Customize Fields, Formula and then select Function Conversion CInt(expression). The conversion function will now appear in the Formula dialog box.

Highlight the word expression by double-clicking on it and select Field Number %Complete. This will now appear in your formula: CInt([% Complete]). Select OK, read the information that pops up, and then select OK and OK again. The %Complete integer now appears alone in Text1.

The Duration is a little bit more complicated. CInt(expression) will convert the expression 10 Space d to the integer 480. If you do NOT use a Space between the 10 and the d, the same number will appear. Insert the same formula in Text2 using the same procedures above to create the formula: CInt([Duration]). The Duration integer will now appear alone in the Text2 field.

 

Dynamically Linking Microsoft Project 2000 Data to Microsoft Excel

 

Now we’re ready to link the data to Excel and create our graph! Open a new Excel spreadsheet, keeping Microsoft Project 2000 open as well. Modify the size of both windows so you can view both programs side by side on your screen. (You can do this by selecting the little square boxes next to the “X” in the upper right hand side of the screen to minimize the viewing area, then place your cursor on the border, left click and move your mouse to reach the desired size).

A picture is worth a thousand words, so set up your Excel spreadsheet Titles in Row 1 to look like Figure One (type just the titles only, NOT the data):

|Name |%Complete |Duration |Integer |

|Task A |30 |  |9600 |

|Task B |25 |  |4800 |

|Task C |100 |  |960 |

Figure One 

Select your desired Tasks in Microsoft Project, then right click and copy. Go to Excel and select the cell below Name, right click and Paste Special. In the dialog box, select Paste Link and select Text in the AS:Box. This will dynamically link the data in Microsoft Project 2000 to Excel. Do the same for %Complete and Duration, copying the converted data in Text1 and Text2, respectively. Double click the title tab Sheet 1 at the bottom of the sheet, and rename it Linked Data. Now test your links by changing the data in Microsoft Project 2000; if the data changes in Excel, your links are successful.

 

You’ve probably noticed that the Duration column is empty, that’s because we need to divide the data in the Integer column by 480 to obtain our original Durations. We don’t do this in Microsoft Project because we need the EXACT data from Microsoft Project to be processed by Excel. So, let's create a formula which divides our data by 480. Select the cell directly under Duration and type in the following formula:

=D2/480 (this is assuming your Integer column is in Column D in your Excel spreadsheet), and click on the little green check on the left to enter the formula. Using the Fill Down feature in Excel, the same one as in Microsoft Project 2000, hold your cursor over the lower right hand corner of the highlighted cell. When a cross appears, click and drag down to fill the cells with the formula. The data is now ready to use!

 

Creating the Graph in Excel

 

We’re going to create our graph on Sheet 1, but we’re going to its final destination on Sheet 2 of the Excel workbook. Rename Sheet 2 “Graph”, return to Sheet 1 and click on the Chart Wizard Icon located in your Toolbar. Note: if the Chart Icon isn’t in the Toolbar, right-click in the Toolbar and select Chart. Click and drag the Chart Toolbar up to the top Toolbar or move it to the side. Click on the Chart Wizard Icon and the Chart Wizard dialog box will appear. For this exercise, we'll use the default bar chart. If your Excel program is not set to use the Bar Chart for the default, just select Column as the Type and select the first Chart that appears in the Chart Sub-type area. Click Next and choose Columns, and then place your cursor on cell A2, click and drag to cell C4 to highlight all the data cells (shown in Figure Two).

 

[pic]

  Figure Two

Click on the Chart Wizard dialog box to see what your graph looks like (shown in Figure Three):

[pic]

Figure Three 

Click Next and title your Chart and Axes, you can select Legend and uncheck the Show Legend box if you don’t want a legend (see Figure Four).

 

[pic]

Figure Four

 

Select Next and place the graph on Sheet 2, select Finish and your graph is now complete. The data in the graph is linked to Microsoft Project 2000 and will reflect any changes in the Task Duration and/or Percent Complete. Your graph should look similar to Figure Five:

 

[pic]

Figure Five

Hope this helps you someday!

Keep on schedulin'………………………Tommy

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download