Hi,
I have a table containing weekly data about a company's revenue and
hours, dependant on department:
Week 1 Week 2 Week
3 Week 4 ... Week 52
DepartmentName $$ hours $$ hours $$
hours $$ hours $$ hours
My Department 1200 15 1800 22 1600
12 2000 15 2200 23
....

Every week has a column for dollars earned and hours spent earning
them.
Now I want to create a report worksheet that would generate some
graphs automatically depending on two variables: week and period. The
week tells me the current week, and the period may be 8 weeks - which
will be used to draw a graph covering the last 8 weeks.

Is it possible to make this user-defined?
I want to be able to have a cell in Excel where I may write the
current week, and one cell to decide the period I need covered for my
graph.

I probably have to do some VB, although I hope to avoid in order to
make the usage of it as easy as possible.
Looking forward to your help.

Royend

Is this what you are looking for. The file is called Man Hour Revenue
Chart.xls.

http://groups.google.com/group/excel...files?upload=1

What I did. First I created a table of your data. Then I put this into a
pivot table. I added a "Revenue per Man Hour" field as I thought you would be
looking for this.

In the Pivot table I placed the Weeks as the main group and the others as
sub groups with departments as the header. I used the chart wizard on the
Pivot table to create a chart for quick use. This should be fairly dynamic.
The only problem with it is that you have to refresh the pivot table to
refresh the pivot table chart.

You could do this another way using an XY scatter but it would be rather
time intensive to make it dynamic. It sounded like you were in a hurry.

God Bless

Frank Pytel

"royend" wrote:

Hi,
I have a table containing weekly data about a company's revenue and
hours, dependant on department:
Week 1 Week 2 Week
3 Week 4 ... Week 52
DepartmentName $$ hours $$ hours $$
hours $$ hours $$ hours
My Department 1200 15 1800 22 1600
12 2000 15 2200 23
....

Every week has a column for dollars earned and hours spent earning
them.
Now I want to create a report worksheet that would generate some
graphs automatically depending on two variables: week and period. The
week tells me the current week, and the period may be 8 weeks - which
will be used to draw a graph covering the last 8 weeks.

Is it possible to make this user-defined?
I want to be able to have a cell in Excel where I may write the
current week, and one cell to decide the period I need covered for my
graph.

I probably have to do some VB, although I hope to avoid in order to
make the usage of it as easy as possible.
Looking forward to your help.

On 1 Sep, 14:14, Frank Pytel
wrote:
Royend

Is this what you are looking for. The file is called Man Hour Revenue
Chart.xls.

http://groups.google.com/group/excel...preadsheet-pro...

What I did. First I created a table of your data. Then I put this into a
pivot table. I added a "Revenue per Man Hour" field as I thought you would be
looking for this.

In the Pivot table I placed the Weeks as the main group and the others as
sub groups with departments as the header. I used the chart wizard on the
Pivot table to create a chart for quick use. This should be fairly dynamic.
The only problem with it is that you have to refresh the pivot table to
refresh the pivot table chart.

You could do this another way using an XY scatter but it would be rather
time intensive to make it dynamic. It sounded like you were in a hurry.

God Bless

Frank Pytel



"royend" wrote:
Hi,
I have a table containing weekly data about a company's revenue and
hours, dependant on department:
Week 1 Week 2 Week
3 Week 4 ... Week 52
DepartmentName $$ hours $$ hours $$
hours $$ hours $$ hours
My Department 1200 15 1800 22 1600
12 2000 15 2200 23
....

Every week has a column for dollars earned and hours spent earning
them.
Now I want to create a report worksheet that would generate some
graphs automatically depending on two variables: week and period. The
week tells me the current week, and the period may be 8 weeks - which
will be used to draw a graph covering the last 8 weeks.

Is it possible to make this user-defined?
I want to be able to have a cell in Excel where I may write the
current week, and one cell to decide the period I need covered for my
graph.

I probably have to do some VB, although I hope to avoid in order to
make the usage of it as easy as possible.
Looking forward to your help.- Skjul sitert tekst -

- Vis sitert tekst -

First of all - I am sorry I didn't reply to this earlier.
It's saturday and it seems there were to many chords (?) to do.

Next: Wow - Thanks!
This excel worksheet is fantastic.
I still haven't been able to study it thouroughly but I will in just
an hour (after dinner). I will give you a reply when I have played
with your application.

Thanks again!

On 1 Sep, 22:52, royend wrote:
On 1 Sep, 14:14, Frank Pytel
wrote:





Royend

Is this what you are looking for. The file is called Man Hour Revenue
Chart.xls.

http://groups.google.com/group/excel...preadsheet-pro...

What I did. First I created a table of your data. Then I put this into a
pivot table. I added a "Revenue per Man Hour" field as I thought you would be
looking for this.

In the Pivot table I placed the Weeks as the main group and the others as
sub groups with departments as the header. I used the chart wizard on the
Pivot table to create a chart for quick use. This should be fairly dynamic.
The only problem with it is that you have to refresh the pivot table to
refresh the pivot table chart.

You could do this another way using an XY scatter but it would be rather
time intensive to make it dynamic. It sounded like you were in a hurry.

God Bless

Frank Pytel

"royend" wrote:
Hi,
I have a table containing weekly data about a company's revenue and
hours, dependant on department:
Week 1 Week 2 Week
3 Week 4 ... Week 52
DepartmentName $$ hours $$ hours $$
hours $$ hours $$ hours
My Department 1200 15 1800 22 1600
12 2000 15 2200 23
....

Every week has a column for dollars earned and hours spent earning
them.
Now I want to create a report worksheet that would generate some
graphs automatically depending on two variables: week and period. The
week tells me the current week, and the period may be 8 weeks - which
will be used to draw a graph covering the last 8 weeks.

Is it possible to make this user-defined?
I want to be able to have a cell in Excel where I may write the
current week, and one cell to decide the period I need covered for my
graph.

I probably have to do some VB, although I hope to avoid in order to
make the usage of it as easy as possible.
Looking forward to your help.- Skjul sitert tekst -

- Vis sitert tekst -

First of all - I am sorry I didn't reply to this earlier.
It's saturday and it seems there were to many chords (?) to do.

Next: Wow - Thanks!
This excel worksheet is fantastic.
I still haven't been able to study it thouroughly but I will in just
an hour (after dinner). I will give you a reply when I have played
with your application.

Thanks again!- Skjul sitert tekst -

- Vis sitert tekst -

Although your example was nice, it did not really cover my problem.
Thanks for your input though!