Limiting Data Obatined By A Graph From A Spread Sheed
 

I would like to make a template to analyze data. I want to be able to just
cut and paste the data into the template spread sheets with graphs and have
it automatically process the info into the graphs. The problem is the number
of data points are not always the same, so the range can not be set. Is
there a way to set the range as all of the valid data in the column. Say set
it to read all of the data in column A and stop taking data when it gets to
empty cells. I'm not sure if this is possible.

David,

You can do this by creating a chart that works via dynamic named ranges. As
an example, try this . . .

(1) Create a new workbook and call it "Tst1.xls".
(2) Rename "Sheet1" to "Test".
(3) Assume the data in your range will at most cover the range B3:C100 (in
reality you can set it to anything you want). The X-axis labels will go in
column B and the Y-axis data points will go in column C.
(4) Go to the standard toolbar and hit Insert - Name - Define. Enter the
following formula and name it "Labels":

=OFFSET(Test!$B$3,,,COUNTA(Test!$B$3:$B$100),1)

(5) Next, in the same name dialog box enter this formula and name it "Values":

=OFFSET(Test!$C$3,,,COUNTA(Test!$C$3:$C$100),1)

When complete, you should have two named ranges called "Labels" and "Values".

(6) Now, create a simple chart using a couple of rows and columns of data
as your chart source.
(7) Click on the chart, go to the Chart option of the standard toolbar, and
go to
Source Date - Series.
(8) In the Series1 - Values field, enter the following:

=Tst1.xls!Values

(9) In the Category (X) Axis Labels field, enter the following:

=Tst1.xls!Labels

When complete, the chart should automatically update for any values you
enter with labels in the range B3:C100.

You might also refer to Jon Peltier's site (http://peltiertech.com) and/or
Stephen Bullen's site (http://www.bmsltd.ie/Excel/Default.htm) look for
"Dynamic Charts".

----
Regards,
John Mansfield
http://www.pdbook.com


"David Hutton" wrote:

I would like to make a template to analyze data. I want to be able to just
cut and paste the data into the template spread sheets with graphs and have
it automatically process the info into the graphs. The problem is the number
of data points are not always the same, so the range can not be set. Is
there a way to set the range as all of the valid data in the column. Say set
it to read all of the data in column A and stop taking data when it gets to
empty cells. I'm not sure if this is possible.