PMConnection

Project Management Books on Kindle

Find and download your favorite from here.

Rolling up Resources in MS Project

Question:

How do I see the effort (work) by resource at a summary task? See the example below

*Summary task

* Child task 1 (4 hs of of PM and 6 hs of a BA)
* Child task 2 (7 hs of of PM and 7 hs of a BA)

I would to see the following information:

* Summary task (11 hs of a PM and 13 hs of a BA)

Answer:

1. Create the scenario:

2. Switch to Resource Usage View:

3. Click on >Project, >Group By, >Customize Group by:

4. Check: Group assignments, not resources

5. Input Task Summary Name as the first Field Name, then select Assignment under field Type.

6. Input Name as the second Field Name.

7. Click on OK

Result:

Desired Result:

Summary task (11 hs of a PM and 13 hs of a BA)

Color Coding Tasks Based Upon Date Range

So you would like to be able to quickly examine your project schedule to see which tasks your team should be working on this week…or…maybe you would like to see which tasks you should work on next week. The following is a dynamic approach, quick to implement, and will meet this need.

1. From within your schedule, click on >View, >More Views, select Gantt Chart, and click on Copy.

2. For Name, type “Date Range View”

3. For Filter, select Date Range…

4. Check Highlight Filter and click on OK. Click on Close to close the More Views dialogue box.

5. Click on >View and select the new >Date Range View

6. A dialogue box will appear asking for you to input the beginning date. Note that this will “Show tasks that start or finish after” the selected date. Always select the first day of the week even if it falls on a Sunday. Click on OK.

7. Another dialogue box will appear asking for you to input the ending date. Note that this will show tasks that start or finish “before” the selected date. Always select the last day of the week even if it falls on a Saturday. Click on OK.

8. Now all of the tasks that are scheduled to be worked on within the selected date range will be automatically highlighted.

Social Bookmark this page

RSS Feed for Exclusive Articles

Export MS Project to Excel, Update, Then Import Updates Back Into MS Project

1. Open the project in MS Project

2. Click on >File, >Save As. Choose the location where you would like this file to be saved.

3. Click on the dropdown for Save as type and select Microsoft Excel Workbook (*.xls)

4. Click on Save

5. The Project Export Wizard will open, click on Next

6. Choose Selected Data and click on Next

7. Choose New Map. Click on Next.

8. Select Tasks and ensure Export includes headers. Click on Next.

9. The Task Mapping dialogue box will appear. Click on the dropdown in the first row under “From: Microsoft Office Project field” and select ID.

10. Click on the dropdown in the second row and input Name.

11. Continue adding the following fields:

ID
Name
Start
Finish
% Complete
Resource Names

12. Click on Next

13. Click on Save Map (for future reuse)

14. Name this map, then click on Save.

15. Click on Finish

16. Let’s say I am the Project Manager for this project and I would like to get updates from my team on their progress. I could attach this Excel file to an email and send it to the team members.

17. Let’s say that the date is 11/07/08 and Angie Krause (a team member) would like to update the progress on her tasks.

18. Open the file in Excel. Click on >Data, >Filter

19. Click on the dropdown for the Resource Names column, uncheck “Select All” and check Angie Krause. Click on OK.

20. Now Angie sees only the tasks assigned to her.

21. The next step is to update the Start date, the Finish date and the % Complete of these tasks. The objective is to bring this information in line with reality.

22. The first three tasks started and completed as expected, so Angie input 100% on those tasks.

23. The fourth task (Task ID 10: Identify budget requirements) is only 50% Complete and instead of completing on 11/07/08, Angie updates the Finish date to be Tuesday 11/11/08.

24. Angie then saves and closes this file and sends it back to the project manager.

25. To incorporate Angies updates back into the project, we must first open the project in MS Project.

26. Within MS Project, click on Open and browse to where Angies updated Microsoft Excel file has been saved. You will need to click on the dropdown in the bottom right to see “All files”.

27. Select Angies updated file in Excel and click on Open. The Import Wizard will again open. Click on Next.

28. Select Use Existing Map and click on Next.

29. Select the map that we saved earlier (Export to Excel) in step 14 and click on Next.

30. Select Merge data into active project and click on Next.

31. Ensure Task and Import includes headers is checked. Click on Next.

32. All fields should already be mapped correctly.

33. Click on ID and then click on Set Merge Key

34. Click on Next, then click on Finish

35. If we drill into the details of this project file and insert the % Complete field, we can see Angies updates have been applied.

36. By examining the Tracking Gantt view, we can see the impact of Angies updates to our baseline.

*A few Cautions:

1. This approach forces Microsoft Project to impose a Finish No Earlier than constraint on any task that is not 100% Complete. As a work-around, the project manager could modify the Excel file prior to distributing so only current period tasks are included.

2. Because we are exporting Task level information, this approach only works with one resource assigned to each task. If you have multiple resources assigned to tasks, you would want to work with the Assignments table on the Export map instead of the Task table.

3. You may want to password protect all columns within the Excel document except Start, Finish and % Complete to keep these fields from being altered.

4. Do NOT add new tasks to your project until after importing the updates. Remember that alignment between the Excel and the MS Project file data is established by the ID column. Inserting a new task will create a new ID thus causing your files to be out of alignment.

Social Bookmark this page

RSS Feed for Exclusive Articles

Top 5 of 2008

Below are the top 5 most visited items from each category within our Research Center for 2008.
If you missed any previous newsletters, this is a great way to catch up!!

Project Management

1	12 steps to better team organization - Podcast
2	Action Plan Template
3	Free Project Terms and Definitions ebook
4	Detailed Project Status Report Template
5	Interview Tips for Project Managers

Microsoft Project

1	6 Page Reference Guide for MS Project
2	How to Create a Master Project with MS Project (video)
3	Creating a Dynamic Project Buffer in Microsoft Project~
4	A First Look at Office Project 2007 - Video
5	All About The Microsoft Project 2007 Certifications

Microsoft Project Templates

1	Construction Schedule
2	Commercial construction project plan
3	Business Start-up
4	PMBOK in MS Project
5	Project management plan

PMP
1
10 Steps to Becoming a PMP
2
Free PMP Exam Prep Flashcards (455 Terms and Definitions)
3
An 8 Point Plan for Passing the PMP Exam
4
298 Study Items for the PMP Exam
5
Errors in the PMBOK Guide and Their Corrections~

PRINCE2
1
Comparing PRINCE2 and PMBOK – 3 Perspectives
2
Marriage of Prince2 with Microsoft Solution Framework
3
How PRINCE2 Can Complement PMBOK® and Your PMP
4
Free Prince2 Reference Card
5
Prince2 Overview and Background

Program Management and PMO
1
3 Must-Haves for a New PMO
2
4 Steps to Building a Successful PMO
3
3 Types of Project Management Offices
4
Why PMOs fail
5
Too Many Templates

PgMP
1
Program Management Professional (PgMP) Sample Questions
2
PgMP Overview Presentation 2
3
PgMP Certification Insights~
4
Navigating the New PgMP Credential
5
New PgMP Credential Links Professional Growth with Value to Employer

Microsoft Project Server
1
Building a PMO with Microsoft's Enterprise Project Management Solution - video
2
Hacking PWA with Web Parts
3
Coordinate Project Server and Windows SharePoint Services security
4
How to Create Document Templates for Document Libraries
5
Add More Time Hierarchy in Project Server 2007 Cubes

Portfolio Management
1
Portfolio Management Framework in Action
2
Aligning Project Workforce With Business and Project Objectives (Template)
3
Project Portfolio Management Maturity: A Benchmark of Current Business Practices
4
2008 Resolutions for IT Portfolio Management
5
Portfolio Selection in EPM - Video

Microsoft Portfolio Server
1
Managing Portfolios with Microsoft Office Project Portfolio Server 2007
2
Deployment Best Practices For Project Portfolio Server
3
Description of Project Portfolio Server 2007 Service Pack 1
4
Selecting the Right Work and Doing the Work Right! Microsoft Portfolio Server Value Briefing - Video
5
Issues that are fixed by Project Portfolio Server 2007 Service Pack 1

Other
1
Project: Humor - Water Me
2
22 Tips To Use At A Networking Event
3
Humorous Project Manager Test
4
Microsoft Opens Office Live Workspace Beta
5
Top Ten Presentation Tips

Here is the top 5 of 2007

See our current Newsletter here.

Sign up for future Newsletters delivered via email here.

Copy Data from Microsoft Project and Paste into Excel

1. Create a view that contains the desired data

2. Select the data you would like to copy within Microsoft Project

3. Click on >Edit, >Copy Task (Ctrl-C)

4. Within Excel, click on Paste

Note that the selected information is pasted accurately, but it lacks the column headings, the WBS or outline structure and it does not retain the bold formatting for Summary Tasks or the Project Summary Task. Also note that the date format now includes the time.

This approach is quick and simple but makes it very tough to decipher the details of this project. Additional work or formatting may be required to get this file in a workable state.

See "Getting Data From Microsoft Project to Excel" for other options.

Social Bookmark this page

RSS Feed for Exclusive Articles

Getting Data From Microsoft Project to Excel

There are a number of ways in which we can get data from Microsoft Project to Excel. Some are easy. Some are harder. Some depend upon the version of Microsoft Project you leverage. This article will list the different ways and the strengths and weaknesses of each approach. Future articles will provide detailed steps on the respective approaches.

Note: This list is built with a maturity model concept. The items that appear first are relatively easy to produce. Options near the end are more sophisticated.

1.    Copy and Paste (all versions of Project)
    a.    Develop the view you like in Microsoft Project, copy the data, move to Excel and paste.
    b.    Plus – Easy!
    c.    Minus – Does not retain outline structure or formatting

2.    Leverage Online Tool (2000 – 2007)
    a.    There is a website you can leverage to convert your Microsoft Project data to Excel
    b.    Plus – Easy and maintains outline structure and formatting
    c.    Minus – Limited to preset fields

3.    Save Microsoft Project as an Excel File (2003 – 2007)
    a.    A nice built in wizard walks you through this process
    b.    Plus – You get to decide which fields get exported
    c.    Minus - Does not retain outline structure or formatting

4.    Analyze Timescaled Data in Excel (2003 and maybe 2007)
    a.    There is a built in feature within Microsoft Project 2003 (and maybe your version of 2007) called Analyze Timescaled in Excel
    b.    Plus – Easy to create, great graphs
    c.    Minus – Does not retain outline structure or formatting, selected fields are displayed below each task

5.    Visual Reports (2007)
    a.    A great new feature of Microsoft Project 2007. Can be found from >Reports, >Visual Reports.
    b.    Plus – Many great pre-configured reports
    c.    Plus – Data can easily be manipulated in Excel using Pivot table
    d.    Minus – To be of real value, you must be good with Pivot tables!

6.    Export Data From Microsoft Project to Excel and Maintain the Outline Structure (2003)
    a.    This is an “old school” work around of saving the file as .html and then converting that to a .xls file.
    b.    Plus – Maintains outline structure and formatting
    c.    Minus – A little tricky if you want to create your own map

7.    Export Data From PWA to Excel and Maintain the Outline Structure (Project Server 2003)
    a.    Simple built in feature of Microsoft Project Server 2003. Drill into Project Details of PWA and then Export Grid to Excel.
    b.    Plus – Easy
    c.    Minus – Requires Project Server, Lacks Timephased data, Getting the data you desire may require building a new PWA View.

8.    Export Data From PWA to Excel and Maintain the Outline Structure (Project Server 2007)
    a.    Simple built in feature of Microsoft Project Server 2007. Drill into Project Details of PWA and then click on >Actions, > Export Grid to Excel.
    b.    Plus – Easy
    c.    Minus – Requires Project Server, Lacks Timephased data, , Getting the data you desire may require building a new PWA View.

9.    Export Data From Microsoft Project to Excel, Update It, and Import Updates From Excel Back into Microsoft Project (2003 and 2007)
    a.    Yes, it’s possible! Requires creating a map.
    b.    Plus – Great for importing updates
    c.    Minus – A bit cumbersome

10.    Macro (2000 – 2007)
    a.    If you are comfortable with macros, then this may be your solution!
    b.    Plus – Retains outline structure and formatting
    c.    Minus – Requires a little knowledge of macros

Social Bookmark this page

RSS Feed for Exclusive Articles

Understanding Colors and Indicators on My Tasks Page

The Tasks page displays your assigned tasks and may display a number of graphical indicators. The following table describes these indicators and their meanings.

Indicator	Meaning	Recommended Action
	The task was added since you last viewed this page.	No action required. If you want to request that this assignment be deleted, delete the new task.
	The Actuals were rejected by the project manager.	Contact your project manager to resolve the problem, and then resubmit the actuals.
	You entered the status and saved it, but the status has not yet been submitted.	No action required. Click Submit Selected when you are ready to send the status to your manager.
	A note is attached to this task.	Click the note indicator to read the note.
	A document is associated with this task.	Select the task, and then select a document from the list to view it.
	A risk is associated with this task.	Select the task, and then select a risk from the list to review it.
	An issue is associated with this task.	Select the task, and then select an issue from the list to review it.

You will also see colored lines next to your tasks.

Below is the key to these colors. Please bear in mind that these colors are meant as guidance and are subject to the influence of pending approvals and updates.

Green - This indicates a New Task

Yellow/Orange - This task will be due soon. Orange will be due sooner than Yellow

Red - Overdue/Past the due date.

Lastly, a pink background on the finish date denotes that it is past today's date.

Social Bookmark this page

RSS Feed for Exclusive Articles

Free Project Management Videos

PM Video Launched by PMConnection

PM Video is a collection of over 250 videos (webcasts, webinars) dealing with Project Management. Why just read an article when you can watch, listen and learn!

These files are categorized by Project Management, Microsoft Project, PMP Certification, Microsoft Project Server, Program Management, and Portfolio Management.

They can be sorted alphabetically, by date added, by rating or by popularity.

Social Bookmark this page

RSS Feed for Exclusive Articles

Dynamic Color Coded Phases

How to Create Multi Color Summary Bar Based Upon Phase

The following steps will show you how to create this:

Note that color formatting is dynamic and will adjust as Duration of the phase increases or decreases.

1. Must establish consistent Phase names. Example:

- Planning
- Execution
- Closure

2. Must establish consistent Milestone names. Also useful to have Acronym for milestone names. Example:

Milestone - Acronym
- Planning Complete – PC
- Execution Complete – EC
- Closure Project - CP

3. Open MS Project

4. Establish Phase Names and Milestone Names:

5. Select the Milestones, right click and click on Task Information.

6. Place a check in “Roll up to Gantt bar to Summary”

7. Click on OK. Rolled up Milestones will now appear on Summary Bars

8. Right click on the Gantt Chart and click on Bar Styles

9. Click on *Rolled Up Milestone, then click on the Text tab. For Bottom, select Name.

10. Click on OK. Milestone Names will now appear below the Rolled up Milestones on the Summary Bars

11. Insert Flag 1, Flag 2, and Flag 3 columns

12. Right click on Flag 1 and click on Customize Fields

13. Click on Rename and input “Planning Flag”

14. Click on OK

15. Click on Formula

16. Input the following formula: [Name]=“Planning”

17. Click on OK

18. For “Calculation for task and group summary rows”, ensure “Use Formula” is enabled.

19. Click on OK

20. “Yes” should now automatically appear under the Planning Flag for the Planning Summary Task:

21. Right click on Flag 2 and click on Customize Fields

22. Click on Rename and input “Execution Flag”

23. Click on OK.

24. Click on Formula

25. Input the following formula: [Name]=“Execution”

26. Click on OK

27. For “Calculation for task and group summary rows”, ensure “Use Formula” is enabled.

28. Click on OK

29. Right click on Flag 3 and click on Customize Fields

30. Click on Rename and input “Closure Flag”

31. Click on OK.

32. Click on Formula

33. Input the following formula: [Name]=“Closure”

34. Click on OK

35. For “Calculation for task and group summary rows”, ensure “Use Formula” is enabled.

36. Click on OK

37. You may now hide the three flag fields

38. Click on Show Outline Level 1

39. Right click on the Gantt chart area and click on Bar Styles

40. Under Name, click on Summary

41. Click on Cut Row

42. Click on Paste Row four times

43. Move to the second Summary under Name and input “Rolled Up Summary Planning”

44. Move to the Show For column, input a coma after Summary, click on the dropdown, select Rolled up. Input a coma after Rolled up, click on the dropdown and select Flag 1. Then click on Rolled up Summary Planning

45. On the Bars tab for Start, Middle and End, click on the dropdown and select the color you would like to appear for the Planning Phase.

46. Complete steps 43 through 45 for the 3rd and 4th Rolled Up Summary

47. Click on OK. Colors will now appear on Summary row

48. To add the Phase Names below each phase, right click on the Gantt Chart and click on Bar Styles

49. Click on Rolled Up Summary Planning under Name

50. Click on the Text Tab

51. For Bottom, Select Name

52. Click on OK

53. Complete steps 49 through 52 for Rolled Up Summary Execution and Rolled Up Summary Closure

54. Click on OK

Social Bookmark this page

RSS Feed for Exclusive Articles

1	10 Steps to Becoming a PMP
2	Free PMP Exam Prep Flashcards (455 Terms and Definitions)
3	An 8 Point Plan for Passing the PMP Exam
4	298 Study Items for the PMP Exam
5	Errors in the PMBOK Guide and Their Corrections~

1	Comparing PRINCE2 and PMBOK – 3 Perspectives
2	Marriage of Prince2 with Microsoft Solution Framework
3	How PRINCE2 Can Complement PMBOK® and Your PMP
4	Free Prince2 Reference Card
5	Prince2 Overview and Background

1	3 Must-Haves for a New PMO
2	4 Steps to Building a Successful PMO
3	3 Types of Project Management Offices
4	Why PMOs fail
5	Too Many Templates

1	Program Management Professional (PgMP) Sample Questions
2	PgMP Overview Presentation 2
3	PgMP Certification Insights~
4	Navigating the New PgMP Credential
5	New PgMP Credential Links Professional Growth with Value to Employer

1	Building a PMO with Microsoft's Enterprise Project Management Solution - video
2	Hacking PWA with Web Parts
3	Coordinate Project Server and Windows SharePoint Services security
4	How to Create Document Templates for Document Libraries
5	Add More Time Hierarchy in Project Server 2007 Cubes

1	Portfolio Management Framework in Action
2	Aligning Project Workforce With Business and Project Objectives (Template)
3	Project Portfolio Management Maturity: A Benchmark of Current Business Practices
4	2008 Resolutions for IT Portfolio Management
5	Portfolio Selection in EPM - Video

1	Managing Portfolios with Microsoft Office Project Portfolio Server 2007
2	Deployment Best Practices For Project Portfolio Server
3	Description of Project Portfolio Server 2007 Service Pack 1
4	Selecting the Right Work and Doing the Work Right! Microsoft Portfolio Server Value Briefing - Video
5	Issues that are fixed by Project Portfolio Server 2007 Service Pack 1

1	Project: Humor - Water Me
2	22 Tips To Use At A Networking Event
3	Humorous Project Manager Test
4	Microsoft Opens Office Live Workspace Beta
5	Top Ten Presentation Tips