how do i update a chart area automatically

Hello,

I have an offset formula I use to update a chart plotted by columns. I name
the ranges and change the range areas to the offset formula...something like
this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes all
the rows available for charting minus the row with the label. I use this
same technique for two columns...Column A acts as the Category x axis and
Column B as the value axis. Works beautifully

Now, I would like to come up with a way to plot by rows. My values would
grow as new field names would be added across the columns...specifically,
Jan, Feb, March and then have April, May and June added to the plot area
automatically.

C138:C147 would have the names of sales reps and D137:F137 would hold the
Month labels and then D138: F147 would list the values. So the list of sales
reps is static, but new data comes in for each month and needs to be added to
the charts. I have many of these charts on a dash board and do not want to
update all of them every month. Any suggestions would be appreciated
alathough I want to use VBA as a last resort.

Thanks!

You use the same idea with the OFFSET formula. The syntax of OFFSET is:

OFFSET(range, offset rows, offset columns, resize rows, resize columns)

so your month label range would have a "refers to" definition like

=OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$137))

This assumes only the dates appear in row 137, and that a date header is not
added until data for the date is added. The easiest way to define the value
ranges with respect to the month range (MonthRange below) is:

=OFFSET(MonthRange,1,0)
=OFFSET(MonthRange,2,0)
=OFFSET(MonthRange,3,0)
etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Kay" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have an offset formula I use to update a chart plotted by columns. I
> name
> the ranges and change the range areas to the offset formula...something
> like
> this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes
> all
> the rows available for charting minus the row with the label. I use this
> same technique for two columns...Column A acts as the Category x axis and
> Column B as the value axis. Works beautifully
>
> Now, I would like to come up with a way to plot by rows. My values would
> grow as new field names would be added across the columns...specifically,
> Jan, Feb, March and then have April, May and June added to the plot area
> automatically.
>
> C138:C147 would have the names of sales reps and D137:F137 would hold the
> Month labels and then D138: F147 would list the values. So the list of
> sales
> reps is static, but new data comes in for each month and needs to be added
> to
> the charts. I have many of these charts on a dash board and do not want
> to
> update all of them every month. Any suggestions would be appreciated
> alathough I want to use VBA as a last resort.
>
> Thanks!

Jon,

I am sorry it has taken me so long to respond to your answer, but I have
been travelling on buisness. I appreciate your responseD!!!

I am not sure I understand your explanation of the MonthRanges. Do I need
to create multiple range names for each column of data that hold the values?

Jan Feb Mar
Salespeople's Names 4589 1234 4589
MORE 2456 4879 12365
MORE 48795 7845 45623

Kay


"Jon Peltier" wrote:

> You use the same idea with the OFFSET formula. The syntax of OFFSET is:
>
> OFFSET(range, offset rows, offset columns, resize rows, resize columns)
>
> so your month label range would have a "refers to" definition like
>
> =OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$137))
>
> This assumes only the dates appear in row 137, and that a date header is not
> added until data for the date is added. The easiest way to define the value
> ranges with respect to the month range (MonthRange below) is:
>
> =OFFSET(MonthRange,1,0)
> =OFFSET(MonthRange,2,0)
> =OFFSET(MonthRange,3,0)
> etc.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> "Kay" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I have an offset formula I use to update a chart plotted by columns. I
> > name
> > the ranges and change the range areas to the offset formula...something
> > like
> > this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically, makes
> > all
> > the rows available for charting minus the row with the label. I use this
> > same technique for two columns...Column A acts as the Category x axis and
> > Column B as the value axis. Works beautifully
> >
> > Now, I would like to come up with a way to plot by rows. My values would
> > grow as new field names would be added across the columns...specifically,
> > Jan, Feb, March and then have April, May and June added to the plot area
> > automatically.
> >
> > C138:C147 would have the names of sales reps and D137:F137 would hold the
> > Month labels and then D138: F147 would list the values. So the list of
> > sales
> > reps is static, but new data comes in for each month and needs to be added
> > to
> > the charts. I have many of these charts on a dash board and do not want
> > to
> > update all of them every month. Any suggestions would be appreciated
> > alathough I want to use VBA as a last resort.
> >
> > Thanks!
>
>
>

Yes, you need to create a name for the months (the X values) and for each Y
series. These names are used in the series definition formula.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


"Kay" <[email protected]> wrote in message
news:[email protected]...
> Jon,
>
> I am sorry it has taken me so long to respond to your answer, but I have
> been travelling on buisness. I appreciate your responseD!!!
>
> I am not sure I understand your explanation of the MonthRanges. Do I need
> to create multiple range names for each column of data that hold the
> values?
>
> Jan Feb Mar
> Salespeople's Names 4589 1234 4589
> MORE 2456 4879 12365
> MORE 48795 7845 45623
>
> Kay
>
>
> "Jon Peltier" wrote:
>
>> You use the same idea with the OFFSET formula. The syntax of OFFSET is:
>>
>> OFFSET(range, offset rows, offset columns, resize rows, resize columns)
>>
>> so your month label range would have a "refers to" definition like
>>
>> =OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$137))
>>
>> This assumes only the dates appear in row 137, and that a date header is
>> not
>> added until data for the date is added. The easiest way to define the
>> value
>> ranges with respect to the month range (MonthRange below) is:
>>
>> =OFFSET(MonthRange,1,0)
>> =OFFSET(MonthRange,2,0)
>> =OFFSET(MonthRange,3,0)
>> etc.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>> "Kay" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > I have an offset formula I use to update a chart plotted by columns. I
>> > name
>> > the ranges and change the range areas to the offset formula...something
>> > like
>> > this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically,
>> > makes
>> > all
>> > the rows available for charting minus the row with the label. I use
>> > this
>> > same technique for two columns...Column A acts as the Category x axis
>> > and
>> > Column B as the value axis. Works beautifully
>> >
>> > Now, I would like to come up with a way to plot by rows. My values
>> > would
>> > grow as new field names would be added across the
>> > columns...specifically,
>> > Jan, Feb, March and then have April, May and June added to the plot
>> > area
>> > automatically.
>> >
>> > C138:C147 would have the names of sales reps and D137:F137 would hold
>> > the
>> > Month labels and then D138: F147 would list the values. So the list of
>> > sales
>> > reps is static, but new data comes in for each month and needs to be
>> > added
>> > to
>> > the charts. I have many of these charts on a dash board and do not
>> > want
>> > to
>> > update all of them every month. Any suggestions would be appreciated
>> > alathough I want to use VBA as a last resort.
>> >
>> > Thanks!
>>
>>
>>

Thank you Jon.
"Jon Peltier" wrote:

> Yes, you need to create a name for the months (the X values) and for each Y
> series. These names are used in the series definition formula.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> "Kay" <[email protected]> wrote in message
> news:[email protected]...
> > Jon,
> >
> > I am sorry it has taken me so long to respond to your answer, but I have
> > been travelling on buisness. I appreciate your responseD!!!
> >
> > I am not sure I understand your explanation of the MonthRanges. Do I need
> > to create multiple range names for each column of data that hold the
> > values?
> >
> > Jan Feb Mar
> > Salespeople's Names 4589 1234 4589
> > MORE 2456 4879 12365
> > MORE 48795 7845 45623
> >
> > Kay
> >
> >
> > "Jon Peltier" wrote:
> >
> >> You use the same idea with the OFFSET formula. The syntax of OFFSET is:
> >>
> >> OFFSET(range, offset rows, offset columns, resize rows, resize columns)
> >>
> >> so your month label range would have a "refers to" definition like
> >>
> >> =OFFSET(Sheet1!$D$137,0,0,1,COUNTA(Sheet1!$137:$137))
> >>
> >> This assumes only the dates appear in row 137, and that a date header is
> >> not
> >> added until data for the date is added. The easiest way to define the
> >> value
> >> ranges with respect to the month range (MonthRange below) is:
> >>
> >> =OFFSET(MonthRange,1,0)
> >> =OFFSET(MonthRange,2,0)
> >> =OFFSET(MonthRange,3,0)
> >> etc.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Peltier Technical Services
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com/
> >> _______
> >>
> >> "Kay" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello,
> >> >
> >> > I have an offset formula I use to update a chart plotted by columns. I
> >> > name
> >> > the ranges and change the range areas to the offset formula...something
> >> > like
> >> > this =Offset(Sheet1! $A$2,0,0,CountA(Sheet1! $A:A)-1). Basically,
> >> > makes
> >> > all
> >> > the rows available for charting minus the row with the label. I use
> >> > this
> >> > same technique for two columns...Column A acts as the Category x axis
> >> > and
> >> > Column B as the value axis. Works beautifully
> >> >
> >> > Now, I would like to come up with a way to plot by rows. My values
> >> > would
> >> > grow as new field names would be added across the
> >> > columns...specifically,
> >> > Jan, Feb, March and then have April, May and June added to the plot
> >> > area
> >> > automatically.
> >> >
> >> > C138:C147 would have the names of sales reps and D137:F137 would hold
> >> > the
> >> > Month labels and then D138: F147 would list the values. So the list of
> >> > sales
> >> > reps is static, but new data comes in for each month and needs to be
> >> > added
> >> > to
> >> > the charts. I have many of these charts on a dash board and do not
> >> > want
> >> > to
> >> > update all of them every month. Any suggestions would be appreciated
> >> > alathough I want to use VBA as a last resort.
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>
>