Thread: Using offset in series values of chart

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

Code:

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:

Code:

=offset(Month,0,7)

In VBA it it can be done with:

Code:

.SeriesCollection(2).Values = Month.Offset(0, 17)

But then I have to create one macro for each graph...again cumbersome.

Please help.

Thx.

Hi Duchbeer

One option is to define the table as a contiguous range. This way, instead of defining a name for each series, you just define a name for the whole table.

Example:
X values in column A and 4 Y series in columns B:E

Define a named range for the whole table

MyTable=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),5)

This code creates a line chart with markers in worksheet Sheet1, based on MyTable:

Code:

Sub CreateChart()

With Worksheets("Sheet1").ChartObjects.Add(100, 100, 600, 400).Chart
    .ChartType = xlLineMarkers
    .SetSourceData Source:=Range("MyTable")
End With
End Sub