Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
User Selectable Series and Number of Series for Line Chart
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
User Selectable Series and Number of Series for Line Chart
Hi,
What is the formula you are using for the defined name? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Dave in NJ" wrote: 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
User Selectable Series and Number of Series for Line Chart
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 "Shane Devenshire" wrote: Hi, What is the formula you are using for the defined name? -- If this helps, please click the Yes button Cheers, Shane Devenshire |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show average line by series on a line chart | Charts and Charting in Excel | |||
Add line series to stacked bar chart | Charts and Charting in Excel | |||
Cutting a line in a line chart when data series stops | Charts and Charting in Excel | |||
chart data series -- plot a table as a single series | Charts and Charting in Excel | |||
how do I change a line series to a column series in excel? | Charts and Charting in Excel |