Using offset in series values of a chart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Beertje - 10 Oct 2007 16:33 GMT

I have 1 named range created with the offset function on sheet(1):

Month=offset($A$1,0,0,counta($A:$A),1)

Simple to use as the x-as categories in a chart.

For the y-axis I want to use data which can be offset 1, 2, up to 20 columns.
Yes I can create 20 named ranges but to do that for 20 sheets in my workbook
gets cumbersome.
Therefor my question:
Can I use the offset function in the series values, like:
=offset(Month,0,7)

In VBA it it can be done with:
.SeriesCollection(2).Values = Month.Offset(0, 17)
But then I have to create one macro for each graph...again cumbersome.

Please help.

Thx.
Beertje.

Reply to this Message

Jon Peltier - 10 Oct 2007 18:50 GMT

You have to create one named range for each set of Y values.

For your charts, if you are making it with VBA, just accept the selected
range from the user, use the first column as the X values, and use each
subsequent column for each series.

' air code
Dim rData As Range
Dim iCol As Long
Dim iChart As Chart
Set rData = Selection
Set oChart = ActiveSheet.ChartObjects.Add(100, 100, 350, 275).Chart
Do While oChart.SeriesCollection.Count > 0
oChart.SeriesCollection(1).Delete
Loop
For iCol = 1 To rData.Columns.Count - 1
With oChart.SeriesCollection.NewSeries
.Values = rData.Columns(iCol + 1)
.XValues = rData.Columns(1)
End With
Next

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

>I have 1 named range created with the offset function on sheet(1):
>
[quoted text clipped - 19 lines]
> Thx.
> Beertje.

Reply to this Message