Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Show average line by series on a line chart Judy Rose Charts and Charting in Excel 1 August 28th 08 04:42 PM
Add line series to stacked bar chart TomB Charts and Charting in Excel 1 April 12th 08 12:04 AM
Cutting a line in a line chart when data series stops DannyS Charts and Charting in Excel 2 August 28th 07 10:38 AM
chart data series -- plot a table as a single series hjc Charts and Charting in Excel 7 September 20th 05 05:52 PM
how do I change a line series to a column series in excel? Mati Charts and Charting in Excel 1 May 12th 05 09:32 AM


All times are GMT +1. The time now is 04:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"