Re: how do i update a chart area automatically

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" <Kay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:486A052D-83FB-4B9C-9AD3-9A8EDD9F031F@xxxxxxxxxxxxxxxx
> 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!


.