Microsoft Project Export to Excel

[Texins Karate]

Using the Microsoft Project "Analyze Timescaled Data in Excel" feature (View,
Toolbars, Analysis), I can export project Resource Work in summary to Excel.
The feature creates a spreadsheet with the timeline on Row 1, Resource Name
on Row 2, and Resource Work on Row 3 by associated time periods for each
named resource in MS Project. In a separate Excel workbook, I list the
"approved" resources that are allocated in my project budget. I need to
ensure that resource names listed in MS Project match the resource names in
my Excel budget. If there is a resource listed in the MS Project export
worksheet that was not listed in the budget worksheet, I want to list the
Resource Name, hours charged within the project timeline. I was going to use
the VLOOKUP or HLOOKUP function to confirm the Resource Name on row 2 of the
export, but unsure how to reference the Resource Work on row 3 for reporting.

Any assistance or suggestions would be greatly appreciated

 

[Texins Karate]

I forgot, I am running MS Project 2003 and MS Excel 2003

 

[JP]

Could you post a few lines of both worksheets?


Thx,
JP

 

[Texins Karate]

MS Project Export:
Months 9/1/2007 10/1/2007 11/1/2007
Resource 1
Work 13.59h 12.70h 2.38h
Resource 2
Work 0.00h 38.15h 1.85h
Resource 3
Work 0.00h 1.00h 0.58h
Resource 4
Work 0.00h 1.00h 0.00h


MS Excel Budget:
9/1/2007 10/1/2007 11/1/2007
Resource 1 13.59h 12.70h 2.38h
Resource 2 0.00h 38.15h 1.85h


Variance Report:
9/1/2007 10/1/2007 11/1/2007
Resource 3 0.00h 1.00h 0.58h
Resource 4 0.00h 1.00h 0.00h

 

[Texins Karate]

I got a response from someone in the MS Project group using a combination of
the ADDRESS and INDIRECT functions:

ADDRESS = returns the location of a specific cell, use this to determine
where the Resource 1 is located. Then change the values for the address to
correspond to the Resource 1 Row + 1. Use this to create a row of addresses
which correspond to the requested date.

To convert the addresses back to data, use INDIRECT on the cells below to
return the value found at each address.

May take a bit of work, but it's not too difficult. If you can do Vlookups,
you should be able to handle this....