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

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/newsg...arts/index.html

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Karine" <Karine@discussions.microsoft.com> wrote in message
news:B0AD92F6-C1BD-47E2-8320-E770C0D3DE74@microsoft.com...
> 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

"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

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" <Karine@discussions.microsoft.com> wrote in message
news:75816F2F-3FF0-41B0-B771-8310704DBB6D@microsoft.com...
> 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

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" <Karine@discussions.microsoft.com> wrote in message
> news:75816F2F-3FF0-41B0-B771-8310704DBB6D@microsoft.com...
> > 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
>

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" <Karine@discussions.microsoft.com> wrote in message
news:AD8A2F32-6553-4231-B6CF-908D790ABF7B@microsoft.com...
> 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" <Karine@discussions.microsoft.com> wrote in message
>> news:75816F2F-3FF0-41B0-B771-8310704DBB6D@microsoft.com...
>> > 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
>>

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" <Karine@discussions.microsoft.com> wrote in message
> news:AD8A2F32-6553-4231-B6CF-908D790ABF7B@microsoft.com...
> > 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" <Karine@discussions.microsoft.com> wrote in message
> >> news:75816F2F-3FF0-41B0-B771-8310704DBB6D@microsoft.com...
> >> > 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
> >>
>

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" <Karine@discussions.microsoft.com> wrote in message
> news:AD8A2F32-6553-4231-B6CF-908D790ABF7B@microsoft.com...
> > 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" <Karine@discussions.microsoft.com> wrote in message
> >> news:75816F2F-3FF0-41B0-B771-8310704DBB6D@microsoft.com...
> >> > 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
> >>
>

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" <Karine@discussions.microsoft.com> wrote in message
news:5D021ED8-05E9-497B-9E36-FAA07500512C@microsoft.com...
> 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" <Karine@discussions.microsoft.com> wrote in message
>> news:AD8A2F32-6553-4231-B6CF-908D790ABF7B@microsoft.com...
>> > 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" <Karine@discussions.microsoft.com> wrote in message
>> >> news:75816F2F-3FF0-41B0-B771-8310704DBB6D@microsoft.com...
>> >> > 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
>> >>
>>

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" <Karine@discussions.microsoft.com> wrote in message
> > news:AD8A2F32-6553-4231-B6CF-908D790ABF7B@microsoft.com...
> > > 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" <Karine@discussions.microsoft.com> wrote in message
> > >> news:75816F2F-3FF0-41B0-B771-8310704DBB6D@microsoft.com...
> > >> > 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
> > >>
> >