Dynamic Named Ranges & Chart Series

Hi,

I'm just getting used to the idea of named ranges as chart series but I'm still confused as to how Excel determines which data to connect with lines.

Please see attached.

I would really like to chart the dotted black parallel lines you see on my chart as dynamic named ranges so it would be automatic no matter how many parallel lines there are (there's just 2 on the data sheet at present). I'm doing it manually, setting 21 seperate series, each with four data points, the X vaules are from column V & U and the Y values are from column S & T. Column V data should connect to Column S data and Column U data should connect to Column T data.

How does Excel determine which sets connect to which?

My attempt to do it with named ranges is the pink line (The last Series in the chart Data: Parallels) that looks nothing like the two dotted parallel lines. I'd like the pink line to replace the dotted ones. Can you see where I've strayed from logic's eternal path?

Any help is appreciated.

Thanks

I still don't get what I'm doing wrong, It's like these named ranges can't be charted properly. Why can't it just take 2 data points from V and S and draw a line to the data points from S & T?

Can I define a named range with two seperate columns that aren't next to each other or does the defined range have to be one block or just one column?

something like this for the named range formula:

Please Login or Register  to view this content.

This syntax is off though. Do you see what I'm trying to do though?

nobody knows the trouble i've seen...

Okay.. lets forget chart and focus on named range only.. now what range you want to refer using a " defined name" - it can be dynamic / simple and mention that in your attachment ?


Regards,
DILIPandey
<click on below * if this helps>

as in the workbook, columns s, t, u & v will vary with the amount of data in them. So, I've created named ranges for all 4 columns.

Okay.. see the defined name "data" which is considering above 4 columns :-

test - 59 - V4.1.xlsx

Regards,
DILIPandey
<click on below * if this helps>

Not sure if I am going to be helpfull here, I have office 2003 and no plugin installed to open the office 2007 files.

However I recently found out that reference points can jump around, for some reason, but making the reference point a named range itself solves it.

To illustrate, this gives me problems:
MonthLabels: =OFFSET('Tabel Uxxxx'!A2;COUNTA('Tabel Uxxxx'!$A:$A)-2;0;-MIN(20;COUNTA('Tabel Uxxxx'!$A:$A)-2);1)
SerieX = =OFFSET('Tabel Uxxxx'!MonthLabels;0;10)

This give me exactly what I want:
StartCell: ='Tabel Uxxxx'!$A$2
MonthLabels: =OFFSET('Tabel Uxxxx'!StartCell;COUNTA('Tabel Uxxxx'!$A:$A)-2;0;-MIN(20;COUNTA('Tabel Uxxxx'!$A:$A)-2);1)
SerieX = =OFFSET('Tabel Uxxxx'!MonthLabels;0;10)

In this example, with more series, I have a chart that always keep the latest entries in a Table in the Graph, but only up to 20 entries/rows.

alright, but how do you chart that? It's just one range. IS that an x value or an Y value. It's both isn't it. So we should probably have 2 named ranges. I'll try and split them up.

No luck, when I try to chart it, it says the values must be in a single column.

Its actually 4 ranges and the date range, I just didnt see the point of posting more than 1 serie.

Where I recently applied it its a chart that reports certain statistics for production units, 4 series:
Early shift: MorningSerie = =OFFSET('Tabel Uxxxx'!MonthLabels;0;9)
Midday shift: AfternoonSerie = =OFFSET('Tabel Uxxxx'!MonthLabels;0;10)
Late shift: NightSerie = =OFFSET('Tabel Uxxxx'!MonthLabels;0;11)
Overall: TRSSerie = SerieX = =OFFSET('Tabel Uxxxx'!MonthLabels;0;12)

Kopie van Uxxx - Production Tracking registratie - v1.3.xls

Attached the file, stripped of data, hope it helps

yea, the latest suggestion is to name range all four columns but that won't chart.

yea, the latest suggestion is to name range all four columns but that won't chart.

Okay... are you saying that :-
Naming those 4 columns as in single name is not working
and
Naming those 4 columns as for different name is not working
??

Regards,
DILIPandey
<click on below * if this helps>

i'm saying the chart won't accept a named range of more than one column and I can't figure out how to put in the single named ranges as chart series to get the desired result.

Based on column A with the code I previously supplied, this selects both colomn B and C
SerieX = =OFFSET('Tabel Uxxxx'!MonthLabels;0;1;-MIN(20;COUNTA('Tabel Uxxxx'!$A:$A)-2);2)


Based on column B
=OFFSET('Tabel Uxxxx'!B2;COUNTA('Tabel Uxxxx'!$B:$B)-2;0;-MIN(20;COUNTA('Tabel Uxxxx'!$B:$B)-2);2)


Untested though

I can't figure out how to put in the single named ranges as chart series to get the desired result.

I can help in that.. now it would be great if you can upload a sample workbook and mention which range to be considered for this purpose. Thanks.



Regards,
DILIPandey
<click on below * if this helps>

I did! The sample workbook is attached to the first post of this thread, where I also state exactly which ranges and results I'm looking for. Thanks

I found that dotted parallel lines are using below data source:-

v1,u1,s1,t1
v2,u2,s2,t2
............

so do you need a defined name for this ?

Regards,
DILIPandey
<click on below * if this helps>

Originally Posted by dilipandey

I can help in that.. now it would be great if you can upload a sample workbook and mention which range to be considered for this purpose. Thanks.



Regards,
DILIPandey
<click on below * if this helps>

are you sure about that?

Hi, seems like you missed my post#16


Regards,
DILIPandey
<click on below * if this helps>