How do I set end-of-range to #rows from beginning

[Jon]

Specifically, I am creating a chart that will have a breadth (# data points)
based on a variable (value of a cell). Start point is always the same. For
instance, a table is created based on a formula. The chart should stop when
values in the table reach a certain value, but this could be 20 or 200
datapoints (20-200 rows). I know how many datapoints should be included, but
not how to terminate the graph based on this number. I would prefer to do
this without VBA, as I am not particularly proficient, but will if the only
way.

 

[Shane Devenshire]

Hi,

It would be nice to see some sample data, with an explanation of what you
want based on that. For example what defines end of range?

Probably the solution should be something like this:

Use dynamic range names

1. Plot your chart using the entire range, lets say A1:B50.
2. Choose Insert, Name, Define and in the Names in workbook line enter X
3. On the Refert to line enter the formula:
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$2:$A$1000),,-24)
4. On the Names in Workbook line enter Y
5. Edit the formula in the Refers to line to read: (change the first
reference from A to B)
=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$A$2:$A$1000),,-24)
4. Click OK.
5. Select the series on the chart and on the formula bar change formula from
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$50,Sheet1!$B$2:$B$50,1)
to
=SERIES(Sheet1!$B$1,Sheet1!X,Sheet1!Y,1)
and press Enter.