SCROLL BAR FOR A 52 WEEK CHART
 

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

SCROLL BAR FOR A 52 WEEK CHART
 

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/newsgr...rts/index.html

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

SCROLL BAR FOR A 52 WEEK CHART
 

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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

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" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

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
"Andy Pope" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

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

"Andy Pope" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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
"Andy Pope" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

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

Karine

"Karine" wrote:

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

"Andy Pope" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

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


"Andy Pope" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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
"Andy Pope" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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

SCROLL BAR FOR A 52 WEEK CHART
 

andy AT andypope DOT info

change the text to form a true email address.

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
ok how do i send the file to you directly? it's still not working.
Thanks


"Andy Pope" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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
"Andy Pope" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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" wrote:

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

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" wrote in message
...
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

"Karine" wrote:

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