Resource usage sheet export to excel

[Dave Carron]

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]

Hi Dave

if you'ld like to email me (***@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

Post by Dave Carron
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

[Dave Carron]

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.

Post by JulieD
Hi Dave
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

Post by Dave Carron
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]

you're welcome :)
--
Cheers
JulieD

Post by Dave Carron
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.

Post by JulieD
Hi Dave
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

Post by Dave Carron
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