Use Scroll Bar to Change Source Data for xlColumnClustered SheetCh

[RyanH]

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("C1010,C1818,C2626,
etc."), ScrollBar.Value = 2 and Week 2 SourceData = Sheets("Summary").Range
("C1111,C1919,C2727, 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!

 

[Andy Pope]

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

 

[RyanH]

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

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