SCROLL BAR FOR A 52 WEEK CHART

[Karine]

Hi there,

I have many charts that are updated on a weekely bases (52 weeks) however
currently I am going in manually and changing the cell rangers to update my
graphs because I only need 10 weeks at a time showing, but I'd like the
option to check the previouse weeks if need be. I know there is a way to add
a scroll bar to the charts and drag that along to update the charts. I can't
seem to figure it out the macro to run. I am using the Scroll Bar from the
Forms toll bar.
Any help would be great.

Thanks,
Karine

 

[Andy Pope]

Hi,

No need for macro, it can be done with dynamic named ranges.

Here is an example of scroll bar control content of chart
http://www.andypope.info/charts/Scrolling.htm

And links to other explanations of dynamic charting.
http://peltiertech.com/Excel/Charts/Dynamics.html
http://tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Cheers
Andy

 

[Karine]

Hey Andy,

Thanks for the information, however the data I have in my tables is used for
multiple graphs and the data goes across rather then down. I tried to do the
Name Define but it keeps reading it downwards rather then across to get the
data. Any tips?

And how do I attach the defined names to the scroll bar?
Thanks again,
Karine

 

[Andy Pope]

Hi,

The OFFSET formula takes 5 arguments.

OFFSET( <AnchorCell> , <StartRow> , <StartColumn> , <NumberOfRows> ,
<NumberOfColumns> )

When dealing with data in row the StartRow and NumberOfRows are usually
specified by formula.
If your data is in Columns then you just need to modify the formula.

You do not attach defined names to the scroll bar. The scroll bar is used to
change a linked cell. The linked cells value is used as the Start or
NumberOf parameter.
Whether you change the Row or Column arguments depends on your data layout.

So if your data is in B2:B20 for example the formula may look like this.

=OFFSET( Sheet1!$B$2 , 0 , COUNTA(Sheet1!$B$2:$U$2)-10 , 1 , 10 )

Cheers
Andy

 

[Karine]

The formula that I was given before is highlighting the cells i'd like and i
have attached the scroll bar to the cell, so thanks for that however, when i
go to my chart options and put in for example =book.xsl!DATA in my X or Y
axis, it gives me an error msg saying
"A formula in this worksheet contains one or more invalid references. Verify
that your formula contains a valid path, workbook, range name, and cell
ferencence"
my formulas look like this,
Data= OFFSET(sheet1!$B$3,0,counta(sheet1!$B$3:$P$3)-52,1,52)
WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52)
Whats wrong with my formula?
In advance thanks again.

Karine

 

[Andy Pope]

Hi Karine,

Is this a simple typo? xsl should be xls

=book.xsl!DATA

Also you appear to have extra $

WEEKS= OFFSET(Sheet1!$B$2$,0,counta(sheet1!$B$2:$P$2$)-52,1,-52)

Try,
WEEKS= OFFSET(Sheet1!$B$2,0,counta(sheet1!$B$2:$P$2)-52,1,-52)

Cheers
Andy

 

[Karine]

Hi Andy,

I'm sorry those were my simple typos when i was writting my question, what
you actually have as a recommondation is what I have on my data. These
Formulas below are copied and pasted.
=OFFSET(PWR!$B$3,0,COUNTA(PWR!$B$3:$P$3)-16,1,16)
=OFFSET(PWR!$B$2,0,COUNTA(PWR!$B$2:$P$2)16,,0,-16)

any other suggestions?
Respectfully,
Karine

 

[Karine]

For clarification I am only doing the first 16 weeks for know.
Thank-you
Karine

 

[Andy Pope]

Hi,

The second formula still looks like it is incorrect.

Try these two
Weeks: =OFFSET(PWR!$B$2,0,COUNTA(PWR!$2:$2)-16,1,16)
Data: =OFFSET(PWR!$B$3,0,COUNTA(PWR!$3:$3)-16,1,16)


If you are still having problems you can email the file to me direct.

Cheers
Andy

 

[Karine]

And i took that extra "," out. still a no go.

Karine

 

[Karine]

ok how do i send the file to you directly? it's still not working.
Thanks

 

[Andy Pope]

andy AT andypope DOT info

change the text to form a true email address.