Dynamically control how many lines on a graph?

[Keith R]

I apologize if this is a FAQ, for some reason my newsreader isn't picking
up any messages from this group (looks like no postings) so hopefully I'll
be able to access responses, if not, I'll googlesearch for responses later
today and tomorrow...
------------------------------------------------
I have many sets of data, all on the same sheet (in a repeated pattern-
starting in row 1, then 11, then 21, etc)

My single graph is based on named ranges, and the named range(s) are based
on Steve Bullen's funchart1 (autoexpanding chart) to control how many
x-axis values are shown- which is great, because each set of data may have
a different number of data points. I control which data set is used on the
graph through a reference cell (A1) which is part of the named ranges, so
it changes what data is shown on the graph automatically.

The problem is that I have "up to" 5 series on each graph, and I need to
show the legend. I know how many series I need on each graph (and can put
that in a cell next to the data), but I need to only "show" the correct
number of series. e.g. if there are 5 series, I want to show them all, and
if there is only one, I want to show only that one on the graph.

In my dreams, there would be a way to link to that cell that shows how many
series should show on the graph, and have it "suppress" any of the
additional (blank) series that are referencing columns of blank data. It
matters
because those extra series affect the column chart's column width and
placement, and it the extra labels still show up on the legend, which makes
the legend bigger, and implies that there is other data there, even when
there isn't.

Does anyone have an easier way to (as automatically as possible)
include/exclude series based on whether there is data to populate those
lines? Best case, a way that preserves formatting when those lines are
reinstated, so I don't have to reformat the reinstated lines each time they
come back? I can't think of a way to do this without VBA, so I'm wondering
if maybe there are some cool chart control options that I've never had to
learn about before that might be helpful.

If it does require VBA and someone else already has a code snippet they'd
be willing to share, I'd appreciate that as well, just to save a little
time on this project so my boss can have the graphs sooner rather than
waiting for me to figure out all the details.

Many thanks,
Keith

 

[dvt]

The problem is that I have "up to" 5 series on each graph, and I need to
show the legend. I know how many series I need on each graph (and can put
that in a cell next to the data), but I need to only "show" the correct
number of series. e.g. if there are 5 series, I want to show them all,
and
if there is only one, I want to show only that one on the graph.

What kind of chart? I ask because your post refers to lines and columns..

If it were a line or xy scatter chart, I could imagine plotting all 5
series. The data for the nonexistent series might be blank cells or
contain N/A errors. The default Excel legend would be replaced by your
custom legend, which would be created using dummy series. A line chart
would probably need to be converted to an XY scatter chart; more work
would be required to work out all the details.

 

[Jon Peltier]

Keith -

This is similar to the Chart by Checkbox example on my web site:

http://www.geocities.com/jonpeltier/Excel/Charts/ChartByControl.html

In brief, there is a checkbox for each series that might be charted.
When each checkbox is checked, it runs a macro that redefines the source
data range. I put an undocumented workbook on my web site:

http://www.geocities.com/jonpeltier/Excel/Zips/ChartByCheckBox2.zip

The checkboxes are linked to cells in the sheet, which are named
"boolY1", "boolY2", and "boolY3". The data for the chart is in ranges
named "theX", "theY1", "theY2", and "theY3". The macro checks each of
the boolYi values, and if it's True, it uses Union to combine that theYi
range with theX and the other theYj ranges with True boolYj. Then it
sets the source data of the chart to this new combined range.

Someday I'll write a new page for this example.

- Jon