How to create a chart based on a 2 dim data range dynamical in 1 d

[Guest]

How can I create a chart based on a 2 dimensional range of data, which is
dynamical in one direction?

Problem: set of data in rows.
A named (dynamic) label range: “wk2005â€=offset($E$2,0,0,1,counta($2:$2)-2).
Named value range: “Dataâ€=offset(wk2005,22,0,5,counta(wk2005)).

Series 1 of chart should have the values of row 22.
Series 2 should have the values of row 23 etc. through to series 5.
The name of the 1st series is placed 3 columns to the left of the first data
point (E24).
The name of the 2nd series is placed 3 columns to the left of the second
data point (E25) etc.
The x-labels is simple: wk2005.

I try to use the offset function in the “source data values box†of the
chart but I cannot get it to work.

Please advise.

 

[Tushar Mehta]

One cannot use OFFSET in the Source Data dialog box of the Chart.
However, create the named formulas first. Then, use the names in the
chart. For more see
Named Formulas
http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/named_formulas.html

and
Names in Charts
http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions