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