Use Scroll Bar to Change Source Data for xlColumnClustered SheetCh
 

I have a SheetChart that has two vertical columns for each of the 17
departments that we have at our company along the X-Axis and Hours along the
Y-Axis. My SourceData is in Sheets("Summary"), Col. C represents Available
Hours and Col.D represents Scheduled Hours. I want to have the ability to
graph each of the 5 Weeks on the same SheetChart. What I would like to do is
embed a ScrollBar that would change the SourceData from Week 1 -- Week 2,
Week 2 -- Week 3, ... , Week 4 -- Future, and visa versa.

I think the best way to setup the code is to change the SourceData row
numbers as the ScrollBar Value changes. For example, ScrollBar.Value = 1
and Week 1 SourceData = Sheets("Summary").Range("C10:D10,C18:D18,C26:D26,
etc."), ScrollBar.Value = 2 and Week 2 SourceData = Sheets("Summary").Range
("C11:D11,C19:D19,C27:D27, etc."), and so on. The ScrollBar Value Min. = 1
and Max. = 5.

Col. C Col.D

Department 1 of 17:
Available Hrs Scheduled Hrs
Week 1 40 40
Week 2 80 50
Week 3 40 30
Week 4 0 0
Future 120 15

Department 2 of 17:
Available Hrs Scheduled Hrs
Week 1 40 40
Week 2 80 50
Week 3 40 30
Week 4 0 0
Future 120 15

And so on....

Note: I have Excel 2003.

Hope this explains everything! Thanks in Advance!
--
Cheers,
Ryan

Use Scroll Bar to Change Source Data for xlColumnClustered SheetCh
 

Hi,

The simplest thing is to build a table of values to be charted.

Add a scroll bar and set Linked cell to F7. Min value = 0. Max value =4

In G9:H9 enter the series name text, Available Hrs and Scheduled Hrs
In F10:F25 enter category labels Department 1 thru to Department 17.
In G10 enter the following formula and copy down and across range G10:H26

=OFFSET($A$1,(((ROW()-ROW($G$10))*8)+ROW($G$10)+($F$7-1)),1)

Create a chart on the range F9:H26.

In E10 enter the formula

=OFFSET($A$1,(((ROW()-ROW($G$10))*8)+ROW($G$10)+($F$7-1)),0)

Add a chart title and then link the chart title to the cell E10.
http://www.andypope.info/tips/tip001.htm

The scrollbar will now move thru Week 1 to Furture.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"RyanH" wrote in message
...
I have a SheetChart that has two vertical columns for each of the 17
departments that we have at our company along the X-Axis and Hours along
the
Y-Axis. My SourceData is in Sheets("Summary"), Col. C represents
Available
Hours and Col.D represents Scheduled Hours. I want to have the ability to
graph each of the 5 Weeks on the same SheetChart. What I would like to do
is
embed a ScrollBar that would change the SourceData from Week 1 -- Week 2,
Week 2 -- Week 3, ... , Week 4 -- Future, and visa versa.

I think the best way to setup the code is to change the SourceData row
numbers as the ScrollBar Value changes. For example, ScrollBar.Value = 1
and Week 1 SourceData = Sheets("Summary").Range("C10:D10,C18:D18,C26:D26,
etc."), ScrollBar.Value = 2 and Week 2 SourceData =
Sheets("Summary").Range
("C11:D11,C19:D19,C27:D27, etc."), and so on. The ScrollBar Value Min. = 1
and Max. = 5.

Col. C Col.D

Department 1 of 17:
Available Hrs Scheduled Hrs
Week 1 40 40
Week 2 80 50
Week 3 40 30
Week 4 0 0
Future 120 15

Department 2 of 17:
Available Hrs Scheduled Hrs
Week 1 40 40
Week 2 80 50
Week 3 40 30
Week 4 0 0
Future 120 15

And so on....

Note: I have Excel 2003.

Hope this explains everything! Thanks in Advance!
--
Cheers,
Ryan

Use Scroll Bar to Change Source Data for xlColumnClustered She
 

I had to alter the formulas to fit my application a bit, but you are dead on.
Your the man. Thanks for the replay!

I like the website too, I'll be saving that one for future reference.
--
Cheers,
Ryan


"Andy Pope" wrote:

Hi,

The simplest thing is to build a table of values to be charted.

Add a scroll bar and set Linked cell to F7. Min value = 0. Max value =4

In G9:H9 enter the series name text, Available Hrs and Scheduled Hrs
In F10:F25 enter category labels Department 1 thru to Department 17.
In G10 enter the following formula and copy down and across range G10:H26

=OFFSET($A$1,(((ROW()-ROW($G$10))*8)+ROW($G$10)+($F$7-1)),1)

Create a chart on the range F9:H26.

In E10 enter the formula

=OFFSET($A$1,(((ROW()-ROW($G$10))*8)+ROW($G$10)+($F$7-1)),0)

Add a chart title and then link the chart title to the cell E10.
http://www.andypope.info/tips/tip001.htm

The scrollbar will now move thru Week 1 to Furture.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"RyanH" wrote in message
...
I have a SheetChart that has two vertical columns for each of the 17
departments that we have at our company along the X-Axis and Hours along
the
Y-Axis. My SourceData is in Sheets("Summary"), Col. C represents
Available
Hours and Col.D represents Scheduled Hours. I want to have the ability to
graph each of the 5 Weeks on the same SheetChart. What I would like to do
is
embed a ScrollBar that would change the SourceData from Week 1 -- Week 2,
Week 2 -- Week 3, ... , Week 4 -- Future, and visa versa.

I think the best way to setup the code is to change the SourceData row
numbers as the ScrollBar Value changes. For example, ScrollBar.Value = 1
and Week 1 SourceData = Sheets("Summary").Range("C10:D10,C18:D18,C26:D26,
etc."), ScrollBar.Value = 2 and Week 2 SourceData =
Sheets("Summary").Range
("C11:D11,C19:D19,C27:D27, etc."), and so on. The ScrollBar Value Min. = 1
and Max. = 5.

Col. C Col.D

Department 1 of 17:
Available Hrs Scheduled Hrs
Week 1 40 40
Week 2 80 50
Week 3 40 30
Week 4 0 0
Future 120 15

Department 2 of 17:
Available Hrs Scheduled Hrs
Week 1 40 40
Week 2 80 50
Week 3 40 30
Week 4 0 0
Future 120 15

And so on....

Note: I have Excel 2003.

Hope this explains everything! Thanks in Advance!
--
Cheers,
Ryan