User Selectable Series and Number of Series for Line Chart

[Dave in NJ]

I have set up a sheet that extracts user selectable data series from a large
data set for up to four series max. I want to chart the data so that if four
series are chosen there are four lines and associated legends; if the user
selects only one series, then only one line and legend should appear.

I have set up a dynamic data range name for the plotted data, that uses the
selected number of series and the number of data points for each series. I've
placed the name in the chart data range on the Chart Tab in Chart/Source Data
as =Sheet1!PlotData

This works fine until the user selects a different number of series to plot.
Then, the data range is no longer associated with the data range name. It
remains fixed at the previous absolute data range (e.g., =Sheet1!$A$!:$E$35).
This is annoying in that I have to go back into the chart source data and
reenter the data range name. In addition, if the number of series is reduced,
the legend for the higher numbered series remains. No data for these higher
numbered series is plotted because I have filled the unused series with N/A
using the NA() function.

How can I get the dynamic data range name to persist after the user changes
the number of series to chart?

I'm using Excel 2004 for Macintosh.

Thanks,

Dave

 

[Shane Devenshire]

Hi,

What is the formula you are using for the defined name?

 

[Dave in NJ]

Hi Shane,

The formula that defines the data range named PlotData is:
=Offset(Sheet1!$a$1,0,0,Sheet1!$a$36,Sheet1!$a$37) where $a$36 contains the
number of data points for each series, obtained by the columns function on
the entire database, and where $a$37 contains the number of series obtained
by counting user inputs. Note that when PlotData is re-entered in the chart
source data range field, the correct data is charted, indicating to me that
the formula defining PlotData name is working properly.

Also, there is a typo in my first post...the parenthetical comment should
read:
(e.g., =Sheet1!$A$1:$E$35)

Dave