# 2D Dynamic Charts

L

Hi,

I have been told and got working dynamic charts and name ranges etc based on
variable amounts of data. Example

I have 5 columns, date, price A, price B, price C, price D. Using the
standard named ranges with the Offset formula
=OFFSET(Sheet1!\$B\$1;1;0;COUNTA(Sheet1!\$B:\$B)-1) applied to each col the chart
updates if there are 3 entries or 30 entries in the month.

However my problem is slightly different. I need to have dynamic series as
well. So perhaps in Jan I need only prices A and D, in Feb I will have 10
prices etc etc.

How can I create a dynamic chart based on variable numbers of series? In
essence a chart input table that will change in length and width. If this is
possible does it matter if I have the series vertical or horizontal, (as in
A1-A5 or A1-E1)?

This is the first time i've tried the dynamic charts so any advice is
probably best in 'idiot guide' form.

Thanks

P

#### Patrick Molloy

could you just making your dynamic range - currently dynamic vertically, also
dynamic horizontally work for you?

ie change
=OFFSET(Sheet1!\$B\$1;1;0;COUNTA(Sheet1!\$B:\$B)-1)

to

=OFFSET(Sheet1!\$B\$1,1,0,COUNTA(Sheet1!\$B:\$B),COUNTA(Range(Sheet1!\$1:\$1)) )

P

#### Patrick Molloy

=OFFSET(Sheet1!\$B\$1,1,0,COUNTA(Sheet1!\$B:\$B),COUNTA(Sheet1!\$1:\$1) )

L

Yeah this formula works but how do i get the legend in the chart to work as
well?

When i delete a column at the moment it will show REF# as it doesnt have any
values to plot

P

#### Patrick Molloy

I expect if you can't use a regular named range in the legend for the chart,
then you'll need to code it. So help needed by somebody expert in graphs ...

L

OK thanks for trying

Patrick Molloy said:
I expect if you can't use a regular named range in the legend for the chart,
then you'll need to code it. So help needed by somebody expert in graphs ...