Resource usage sheet export to excel

I am using the resource usage sheet as a method to create a rolling 6 week
work schedule for a number of resources. The method I am using is a bit
cumbersome and I am wondering if there is an easier way to do it. What I do
each week is as follows:-

1. Go to resource usage view and expand all resource assignments.
2. Make sure the timescale is formatted to show at least 6 weeks.
3. Select columns from the table part (resource, id, work, remaining work,
actual work and several dates [I have modified this table]) and cut and paste
to excel.
4. Select 6 weeks of work columns and cut and paste to excel.
5. Convert the work values from text e.g. "3.5d" to equivalent hours using
formulas in excel. (Can this step be avoided by getting project to output
numbers ?)
6. From a seperate project task view cut and paste the task id and a custom
text field into another worksheet and use vlookup to get the custom text into
my work schedule. (I have not found a way to get a task field to appear in a
work view).
7. Further manipulate the data in excel and use a pivot table to get the
work schedule by resource.
8. Have a lie down in a dark room !

I'm assuming there must be an easier way ? I'm using MS Project 2000 SR-1.

Thanks,

Dave Carron

JulieD
Thu Apr 21 11:20:13 CDT 2005

Hi Dave

if you'ld like to email me (julied_ng@hcts.net.au) a sample of your final
excel workbook i'll be interested in having a go at this question

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"Dave Carron" <DaveCarron@discussions.microsoft.com> wrote in message
news:BB922271-F890-4BC9-ADE4-25DEC5186B89@microsoft.com...
>I am using the resource usage sheet as a method to create a rolling 6 week
> work schedule for a number of resources. The method I am using is a bit
> cumbersome and I am wondering if there is an easier way to do it. What I
> do
> each week is as follows:-
>
> 1. Go to resource usage view and expand all resource assignments.
> 2. Make sure the timescale is formatted to show at least 6 weeks.
> 3. Select columns from the table part (resource, id, work, remaining work,
> actual work and several dates [I have modified this table]) and cut and
> paste
> to excel.
> 4. Select 6 weeks of work columns and cut and paste to excel.
> 5. Convert the work values from text e.g. "3.5d" to equivalent hours using
> formulas in excel. (Can this step be avoided by getting project to output
> numbers ?)
> 6. From a seperate project task view cut and paste the task id and a
> custom
> text field into another worksheet and use vlookup to get the custom text
> into
> my work schedule. (I have not found a way to get a task field to appear in
> a
> work view).
> 7. Further manipulate the data in excel and use a pivot table to get the
> work schedule by resource.
> 8. Have a lie down in a dark room !
>
> I'm assuming there must be an easier way ? I'm using MS Project 2000 SR-1.
>
> Thanks,
>
> Dave Carron

DaveCarron
Mon Apr 25 11:51:06 CDT 2005

Julie,

Thankyou so much for this, it works perfectly. Just in the process of
modifying it to include some more fields. You have saved me a large amount of
time, but even better I can now see how to use VBA to automate many of the
other analysis tasks I am doing. This means more time for planning and
analysing - and a better plan ! Thanks again, I really appreciate it.


"JulieD" wrote:

> Hi Dave
>
> if you'ld like to email me (julied_ng@hcts.net.au) a sample of your final
> excel workbook i'll be interested in having a go at this question
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Dave Carron" <DaveCarron@discussions.microsoft.com> wrote in message
> news:BB922271-F890-4BC9-ADE4-25DEC5186B89@microsoft.com...
> >I am using the resource usage sheet as a method to create a rolling 6 week
> > work schedule for a number of resources. The method I am using is a bit
> > cumbersome and I am wondering if there is an easier way to do it. What I
> > do
> > each week is as follows:-
> >
> > 1. Go to resource usage view and expand all resource assignments.
> > 2. Make sure the timescale is formatted to show at least 6 weeks.
> > 3. Select columns from the table part (resource, id, work, remaining work,
> > actual work and several dates [I have modified this table]) and cut and
> > paste
> > to excel.
> > 4. Select 6 weeks of work columns and cut and paste to excel.
> > 5. Convert the work values from text e.g. "3.5d" to equivalent hours using
> > formulas in excel. (Can this step be avoided by getting project to output
> > numbers ?)
> > 6. From a seperate project task view cut and paste the task id and a
> > custom
> > text field into another worksheet and use vlookup to get the custom text
> > into
> > my work schedule. (I have not found a way to get a task field to appear in
> > a
> > work view).
> > 7. Further manipulate the data in excel and use a pivot table to get the
> > work schedule by resource.
> > 8. Have a lie down in a dark room !
> >
> > I'm assuming there must be an easier way ? I'm using MS Project 2000 SR-1.
> >
> > Thanks,
> >
> > Dave Carron
>
>
>

JulieD
Mon Apr 25 11:54:09 CDT 2005

you're welcome :)

--
Cheers
JulieD

"Dave Carron" <DaveCarron@discussions.microsoft.com> wrote in message
news:B032C600-C45A-43F8-882C-4BBAA940AF23@microsoft.com...
> Julie,
>
> Thankyou so much for this, it works perfectly. Just in the process of
> modifying it to include some more fields. You have saved me a large amount
> of
> time, but even better I can now see how to use VBA to automate many of the
> other analysis tasks I am doing. This means more time for planning and
> analysing - and a better plan ! Thanks again, I really appreciate it.
>
>
> "JulieD" wrote:
>
>> Hi Dave
>>
>> if you'ld like to email me (julied_ng@hcts.net.au) a sample of your final
>> excel workbook i'll be interested in having a go at this question
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "Dave Carron" <DaveCarron@discussions.microsoft.com> wrote in message
>> news:BB922271-F890-4BC9-ADE4-25DEC5186B89@microsoft.com...
>> >I am using the resource usage sheet as a method to create a rolling 6
>> >week
>> > work schedule for a number of resources. The method I am using is a bit
>> > cumbersome and I am wondering if there is an easier way to do it. What
>> > I
>> > do
>> > each week is as follows:-
>> >
>> > 1. Go to resource usage view and expand all resource assignments.
>> > 2. Make sure the timescale is formatted to show at least 6 weeks.
>> > 3. Select columns from the table part (resource, id, work, remaining
>> > work,
>> > actual work and several dates [I have modified this table]) and cut and
>> > paste
>> > to excel.
>> > 4. Select 6 weeks of work columns and cut and paste to excel.
>> > 5. Convert the work values from text e.g. "3.5d" to equivalent hours
>> > using
>> > formulas in excel. (Can this step be avoided by getting project to
>> > output
>> > numbers ?)
>> > 6. From a seperate project task view cut and paste the task id and a
>> > custom
>> > text field into another worksheet and use vlookup to get the custom
>> > text
>> > into
>> > my work schedule. (I have not found a way to get a task field to appear
>> > in
>> > a
>> > work view).
>> > 7. Further manipulate the data in excel and use a pivot table to get
>> > the
>> > work schedule by resource.
>> > 8. Have a lie down in a dark room !
>> >
>> > I'm assuming there must be an easier way ? I'm using MS Project 2000
>> > SR-1.
>> >
>> > Thanks,
>> >
>> > Dave Carron
>>
>>
>>