2D Dynamic Charts

[LiAD]

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
LiAD

 

[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)) )

 

[Patrick Molloy]

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

 

[LiAD]

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

 

[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 ...

 

[LiAD]

OK thanks for trying

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 ...