Linking Excel Data into MS Project

[kaney]

Problem
We have PMs updating excel reports with information in numerous cells in no particular order.
Rather than having the PMS update these excel reports and project plan templates I have been asked to investigate the following:
Can we provide links from the excel cells to go direct into the MS Project plan 2003 template so on a weekly basis they just update excel report and the new version is linked to the MS Project Plan which will update Accordingly any dates or text fields etc.
IS there any issues i.e. file size or problems with the data being linked i.e. dependencies??? or is it easier that they just update the excel reports and an MS Project Plan
Any advice is appreciated

 

[PDQBach]

You can do the "link" thing but I won't tell you how to do it: It's just too cumbersome and too awkward.

First, you'll have to spend time building the links -- figure that there are probably a half-dozen fields you want to link and probably 300 tasks ... that means you have to create 1800 links.

On top of that, you might want to also include links to resources -- so now you have to create links there, too.

What happens when you add a task or resource (probably both) to the spreadsheet? There won't be links between that new information and the existing schedule. How will you identify those tasks? resources? What about deletions?

In addition, some fields are calculated by Project and still others where, if you link the field, Project will impose constraints, etc.

You are far better off to copy the information from Project and paste it TWICE into Excel. Paste the Project columns into Excel columns A through Z and then again into columns AA through AZ. Then set up conditional formatting comparing (for example) cell C5 with cell AC5 and have cell C5 appear in bold red if the two values don't match. That way you'll immediately know which cells were changed.

In fact, things won't be quite that easy because you'll want to allow for column headers, etc., but that's another 30 seconds at most. And when it comes to doing the conditional formatting, a quick macro and you're done. (For Excel macro help you'll have to go to the Excel forum.)

 

[SkipVought]

(For Excel macro help you'll have to go to the Excel forum.)

Forum68.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

(For Excel macro help you'll have to go to the Excel forum.)

Forum68. Forum707.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kaney]

Thanks for response