series address

[Guest]

How do I get the series address in VBA? I want to dynamically change the
chart title based upon which column is used for the Y-values in an X-Y
scatter plot.

wazooli

 

[Jon Peltier]

This requires you to parse the SERIES formula, which sounds like a major
pain, unless someone tells you that John Walkenbach has a class module
you can use that simplifies all that:

http://www.j-walk.com/ss/excel/tips/tip83.htm

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

 

[Guest]

Well, I am of course one to shy away from something that is a "major pain".
Can you suggest another way to have the chart title update based upon which
series is chosen? I have an X-Y scatter plot embedded in a worksheet, with
the following ranges used:

$D$3:$D$6756 (x-series) and $E$3:$L$6756 (y-series).

I like being able to change the chart simply by moving the blue selection
box from column to column. The chart title is contained in D2:L2, so I would
need the chart title to update from those cells depending upon which column
is used for the y-series. Any help is appreciated.

wazooli

 

[Jon Peltier]

1. First, I said "sounds like" a major pain. John's class module makes
it easier after a very short learning curve.

2. Chapter 7 of John Walkenbach's book, Excel Charts, has a section
entitled Plotting a Series Based on the Active Cell. You could update
the chart by moving the active cell rather than dragging the blue
outline, and everything would stay aligned.

3. You could also handle this by changing a number in a cell which was
used in the Offset formula defining the title and chart range. This in
turn could be linked to a scroll bar or other control, giving your chart
that intangible coolness. See these pages for info:

http://peltiertech.com/Excel/Charts/ChartByControl.html
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

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

 

[Guest]

Well, what may not be a major pain for you, thanks to the class module, is
certainly a pain for me, a VBA novice. However, I kinda like the idea of
using a control to change the chart. I will have to investigate that one.

wazooli