How to link data between Project and Excel 1

[alwayslrN]

We are using Microsoft Project 2003 - my boss created an Excel spreadsheet with a list of enhancements. The list contains an ID NO., requester, description (not a task name, but rather a summary of the issue), priority, rank, status, and notes. A coworker created a project plan to turn the requested items into a project, however, there is noting in the plan to tie the spreadsheet to the tasks. I only know the basics of Project, add a task and create dependencies. Does anyone have any suggestions on how to at least at the ID NO. from the spreadsheet to the project plan?

Excel example
ID NO. Description Priority Rank Status
1 There is problem w/x. 1-High 1 Identified

2 There is problem w/y. 1-High 2 Identified

3 There is problem w/z. 1-High 3 Identified

4 There is problem w/y. 2-Med 4 Identified

In Project there is the WBS structure that does not correspond to the above, especially since each of the above requires several steps to handle the issue in the description.

Is there a way to make the data from each speak to each other?

Thanks

 

[PDQBach]

Yes there is but it is definitely fragile and I think so little of it that I won't tell you how to do it.

The real issue is a business process one. You need to decide which document is the prime document and then stop using the other one.

You can (probably should) consider that the Project schedule is the prime document (at least after the initial data capture from the Excel spreadsheet).

Whenever it is necessary for your boss to provide you with updated info all you have to do is a simple copy&paste from Project to Excel and then some formatting. I do stuff like this all the time and it takes at most 5 minutes to set up Project so that it is displaying the columns I want, do the copy&paste and then do the necessary formatting to make the spreadsheet look nice and pretty. Then give him the spreadsheet and tell him to make his updates there and you update the schedule with the information he has changed.

As a fancy trick, I often save the copied information in Sheet1 *and* Sheet2. Then I use the conditional formatting of Excel and set it so that if a cell on Sheet1 is not equal to the same cell on Sheet2 then it shows up in red so I can quickly see what cells have been changed. It only takes a couple of minutes to set that up.

For your "special columns" requirement in Project, just display the Number1 column and use it to hold the "Rank" info, use Text1 to hold the "Status" and you could get clever and use the Custom Outline Code functionality and Outline Code 1 to hold the Priority information.

 

[alwayslrN]

Thanks I will give your suggestions a try