MS Project -> Excel Tasks to columns

[Griphus]

Hi everyone,

Summary:
I am looking for an efficient way to display quasi-standard tasks for multiple (~60) projects that we are tracking in MS project as columns in Excel. Because the tasks are quasi standard, we give each task a column, each project a row, and create a matrix of finish dates in Excel.

Long version:
I work for a manufacturing company that currently has no capacity tracking. I am setting them up with an MS Project resource pool and then projects for each order. Everyone here is used to seeing one Excel spreadsheet that gives each project a row, with the stuff needed to build that project as columns. The goal is to create an MS Project back end that dynamically (or with as little work as possible) updates this Excel front end.

I was originally hoping to use copy/paste special -> link -> text from Project to Excel. However, that is quickly turning into a nightmare as the folks actually maintaining the system aren't super tech savvy.

Example:

           Task 1   | Task 2   | Task 3
Project 1  11/30/11 | 12/15/11 | 12/25/11
Project 2  12/15/11 | 12/25/11 | 01/05/12
Project 3  02/08/12 | 02/12/12 | 02/24/12

Let me know if you have any questions or if there's information I didn't provide that would help you.

Thanks very much!
Timothy

 

[SkipVought]

Hi,

Please explain what you will be using the Excel workbook for.

Please explain in more detail how you plan to export the data. It can be parsed quite easily in Excel. Please post your Excel questions in forum68 and Excel VBA (macro) questions in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I would be more apt to store my data in a normalized table, that would make this data infinitely more usable to analyse and report in Excel, than the strictly report format that you posted. This transformation is actually quite simple.
[tt]
ProjID TaskID Dte
Project 1 Task 1 11/30/2011
Project 1 Task 2 12/15/2011
Project 1 Task 3 12/25/2011
Project 2 Task 1 12/15/2011
Project 2 Task 2 12/25/2011
Project 2 Task 3 1/5/2012
Project 3 Task 1 2/8/2012
Project 3 Task 2 2/12/2012
Project 3 Task 3 2/24/2012
[/tt]
Not sure if your date is a start, or end date

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Griphus]

Hi Skip,

Thanks for the response. The formatting is for a large meeting all the senior management has each morning, not for analysis. It's a reporting tool more than anything. They are all very used to seeing this report a certain way, and we want to keep it the same, so they continue to accept it.

If that is still not clear, I can upload an example tomorrow morning when I'm back in the office.

 

[SkipVought]

What version of Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Griphus]

So this is the fun part.

I am on 2003, but we're a huge company and it's not standard at all. There will be 2003, 2007, and 2010.

 

[SkipVought]

I can help you, and so can members at forum68.

If you export a text file, you can, in 2007, Data > Get external data > from text -- and drill down to you file. The import window will enable you to specify DELIMITED, the next window you can adjust the parse, the next window you can control what to do with each segment.

You should end up with data in the columns you want.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]