Using offset in series values of chart

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

In VBA it it can be done with:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.