SCROLL BAR FOR A 52 WEEK CHART

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top