dynamic charts

[Guest]

I've been reviewing the helpful links given in this forum for dynamic charts,
and I've learned a great deal. All of the links I've reviewed focus on the
ability to have the range of data points displayed for a GIVEN # of series be
dynamic, but in all those cases the number of series was static. They focus
on editing the SERIES formula with COUNTA and OFFSET functions, so they can
move. In the problem I'm working on I need to have the actual number of
series be dynamic.

In my example...

0 1 2
a 5.57% 4.35% 3.66%
b 5.58% 3.44% 2.05%
0 0.00% 0.00% 0.00%
0 0.00% 0.00% 0.00%


I'm using a line chart. I have condition 0,1, and 2 and I have series a&b.
The zero cases(what would be series c&d) are automatically filled in if
another condition is met. But the chart has to be modified each time. If I
understand the dynamic charting correctly, I could add a condition 3, 4 and 5
and have them automatically reflected in the chart, but I'd like the chart to
recognize when additional series c&d are added. Is this possible?

Hoping chart MVP's are around. Thanks,
Dave

 

[Jon Peltier]

Dave -

It's possible to define a range in a worksheet that resizes with rows
and columns. You need a little VBA to make it update the chart in both
dimensions.

The VBA is very simple. Define a range called "myrange", which refers to
the rectangle that contains the X and Y values and series names. Right
click on the sheet tab, and select View Code. In the code module that
appears, paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("myrange")) Is Nothing Then
ChartObjects(2).Chart.SetSourceData Range("myrange")
End If
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Thanks for replying. I tried the code, but the chart still shows the series
that have zero values. I'd like the chart to only show the series whose
values(and label) are greater than zero. If it helps - I don't need to
resize anything in the chart other than the number of series. The number of
x-values remains the same, and the y values are automatically scaled based on
the data results.
Let me know if there is any additional information I can give you.

tia,
Dave

 

[Jon Peltier]

Dave -

I was thinking you had blanks at the bottom of the list, not zeros
within the list. You could set up an AutoFilter (Data menu), and hide
any rows that have a zero or blank in the first column. By default, rows
hidden manually or by a filter are not included in a chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Jon,

Thanks alot - that works. One last thing. After I've set up the
autofilter, the correct rows disappear and the chart displays it correctly.
After I change some source data, the zeros become numbers in an additional
column within the range of the chart source range. But the row which was
added does not automatically unhide since its not zero. I have to use the
autofilter pull down menu and reselect my custom option to redisplay the
nonzero row.

Will I have to live with this one or is there a setting which I'm missing?

Dave

 

[Jon Peltier]

You could have the Worksheet_Calculate event reset the filter, or put a
button on the sheet (one click is better than navigating a tight little
dropdown). Record a macro while you reset the filter manually, to get
the syntax.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______