Dynamic chart ranges

[Russ]

I have workbook with several sheets that contain x-y data sets. Each
has a single column of x values followed by several columns of y
values. The definition of the x and y is the consistent between sheets
however the number of data points varies from sheet to sheet.

I made a summary sheet using combo boxes that allows a user to look at
one specific data set (based on sheet name), and one specific y
variable (based on column number). The summary sheet lists the
specified variables and plots them on a simple x-y plot.

The issue is that some data sets have 200 rows and some have 10,000.
If I include enough rows to accommodate the larger set, the x-axis on
the plot includes 10,000 values. If I then switch to the set with 200
points the plot still has 10,000 values with 9,800 equal to zero. This
requires the user to manually set the min/max range of the plot to
display the data every time a new set is selected.

Is it possible to set up the sheet so that the x-axis will
automatically adjust and plot only the non-zero values?

Thanks.

 

[Herbert Seidenberg]

Excel 2007 PivotTable, PivotChart
Consolidate X Y data from multiple sheets.
Select sheets, Y sets from drop-down list.
Tables and chart all dynamic.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_02_10.xlsx