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