Dynamic Range Charting

[BarryL]

I am charting the number of units in various territories. I am trying to
create a data range that varies depending on the number territories being
analysed. I set the range to be large enough to accomodate the largest group
(e.g. when there are 11 territories to chart my data range is e3:f13, but
when there are only 3 territories the actual data in this range is confined
to the cells e3:f5.

Even though my formulas within the unused cells in my data range eliminate
the values and replace them with a blank cell, the chart legend still shows
the colours for the missing territories with a blank beside each colour in
the legend (because my data range is still e3:f13 even though I only have 3
rows of actual data).

How do I make my data range adjust to accomodate only those rows actually
being charted and eliminate the unnecessary rows that still exist in the data
range?

 

[Jon Peltier]

You could use an autofilter on the data, to hide the rows corresponding to
territories you want to omit. Alternatively you could use VBA to change the
source data range of the chart. Unfortunately dynamically defined names
cannot be used to change the number of series in a chart, only the number of
points in a series.

- Jon