LinkBack URL
About LinkBacksYes, 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" <Kay@discussions.microsoft.com> wrote in message
news:93C0B3D1-62F6-4122-9FF3-2B52FCC3B5F5@microsoft.com...
> 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" <Kay@discussions.microsoft.com> wrote in message
>> news:486A052D-83FB-4B9C-9AD3-9A8EDD9F031F@microsoft.com...
>> > 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!
>>
>>
>>
Register To Reply
Bookmarks