K
Keith R
I have many sets of data, all on the same sheet (in a repeated pattern-
starting in row 1, then 11, then 21, etc)
I use an indirect function with named ranges to change which set of data is
showing on my (single) graph. That way I can change just a single cell, and
have the graph show a different set of data.
I use a modification of 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 also have a cell with a formula that tells me how many total (graph)
lines are in that overall chunk of data (e.g. if only Col G has data, the
cell returns a 1, if H has data, it returns a 2, etc.). This tells me how
many lines should be on the graph
In my dreams, there would be a way to link to that cell that shows how many
lines should show on the graph, and have it "suppress" any of the
additional lines that are referencing columns of blank data. It matters
because those extra "lines" 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.
My alternative is to capture the cell change event, compare to see if my
target cell is the one that changed, and if so, add or delete lines via VBA
until I have the correct number of lines showing, then (because I can't use
Excel's default formats) reformat any lines that have been added.
Does anyone have an easier way to (as automatically as possible)
include/exclude lines based on whether there is data to populate those
lines? Best case, a way that preserves formatting when those lines are
reinstated? 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.
Many thanks,
Keith
starting in row 1, then 11, then 21, etc)
I use an indirect function with named ranges to change which set of data is
showing on my (single) graph. That way I can change just a single cell, and
have the graph show a different set of data.
I use a modification of 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 also have a cell with a formula that tells me how many total (graph)
lines are in that overall chunk of data (e.g. if only Col G has data, the
cell returns a 1, if H has data, it returns a 2, etc.). This tells me how
many lines should be on the graph
In my dreams, there would be a way to link to that cell that shows how many
lines should show on the graph, and have it "suppress" any of the
additional lines that are referencing columns of blank data. It matters
because those extra "lines" 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.
My alternative is to capture the cell change event, compare to see if my
target cell is the one that changed, and if so, add or delete lines via VBA
until I have the correct number of lines showing, then (because I can't use
Excel's default formats) reformat any lines that have been added.
Does anyone have an easier way to (as automatically as possible)
include/exclude lines based on whether there is data to populate those
lines? Best case, a way that preserves formatting when those lines are
reinstated? 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.
Many thanks,
Keith