Reassigning Chart Data Series

[jim may]

I had previously assigned into the Data Series Box of my chart (xl2007):

='CurrYear-View'!$C$6:$K$22

Since then I created a dynamic Range Name -- MyCurrYr with RefersTo:
=Offset($C$6,0,0,17, Sumproduct(--($C$8:$K$8<>"")))

When I try and replace the Data Series (box)
='CurrYear-View'!$C$6:$K$22
with
=MyCurrYr - I get error message saying "check the formula.....etc..

If I enter it (in the box) as just MyCurrYr (without the "=" I'm able to
OK out without an error. I have SAVED the Workbook immediately afterwards. But each time I go back an view the Data Series Box - it still reads the original

='CurrYear-View'!$C$6:$K$22

It is NOT Sticking to the wall !!! LOL Any Comments of assistance?

Jim

 

[Steve=True]

I believe that this will continue to happen to you as long as you are modifying the chart from Chart Data Range at the top.

In order to make refer to the named ranges, you must put them in each individual series as such:

=Filename!Name


Steve=True
www.exceldashboardtemplates.com

 

[pgc01]

Hi Jim

If I'm not mistaken that will really not work. I don't think that you can have the chart source range dynamic, only series (I'd be happy to be proven wrong).

If you would be plotting the series by rows, you could use Steve's method and define named ranges for the series.


It seems to me, however, that you want to plot the values by columns. This means that what you want is not series expanding/contracting, but a variable number of series according to the values in row 8.

To have a variable number of series, you'll have to choose an adequate solution to your case:

Some ideas:

1 - if there's some cell the that the user changes that should trigger a chart update, use the worksheet Change event

2 - define the chart with the maximum number of series and make them visible or invisible by changing the values in the table. For ex., if the values are calculated with formulas, when you don't want the series to be displayed have the formulas return #N/A for that series.

3 - define the chart with the maximum number of series and make them visible or invisible using vba when the values in the table change.

4 - use a button to update the chart.

...

Don't forget that you'll have to tackle an usual problem with the variable number of series that is the Legend. Just making the series invisible will not make the Legend entry go away, you have to do it yourself, with vba.

Remark:

Depending on the solution you choose you can still use your named range:

Worksheets("CurrYear-View").ChartObjects("MyChart").Chart.SetSourceData _
    Source:=Worksheets("CurrYear-View").Range("MyCurrYr"), PlotBy:=xlColumns

It's just that it will not result in the dynamic chart the way you want, you'll have to execute the statement each time the data changes.