If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|