How can I limit the range of data graphed by the value of another column?

[BrianW]

The question is hard to ask, which is why I haven't found the answer.

The spreadsheet has lots of data, but I want to graph a subset of the
data at a time. One column contains an integer that is incremented
every 700-1000 rows (ie 1..1,2..2,etc.) which is the event number, and
there are 2 other columns of data that I want to plot. So I want to
plot one chart with all the data with the event number of 1-3, another
4-6, etc. I know I can just specify the data range with the row
numbers that correspond to the event number range I want, but is there
a way excel can do the work for me?

I am trying to make the process of creating these graphs an automated
process, because the analysis of this type of data is going to be
going on on a regular basis, and will always have the same event
numbers.
Thanks,
Brian

 

[duane]

sounds like you want to id the rows via the match function

=match(4,columnwhatever,0) will give you the row number with the firs
4

then you can use the offset function

=offset(top cell,the match function,0,1,1) helps you define the range

ie something like

the chart range = c2ffset(c2,match(4,a2:a1000,0)

 

[CptanPanic]

It worked good, except that when I enter in the formula for the char
range, excel only calculates the forumula the first time.

So that if the values change in the spreadsheet which causes the rang
to change, the chart stays the same. And I have to go back and type i
the forumula again.

What else do I need to do?

C

 

[duane]

have you "hard coded" the "4" in my example? if so, you can make thi
refer to the cell where the value changes, so that the match functio
result change

 

[Jon Peltier]

If you want dynamic ranges in the chart, you have to assign them to the X and Y
ranges of each chart series. The Data Range doesn't remember the name you assigned,
only its address.

The practical effect of this is you can only make the length of each series dynamic,
but not the number of series. To make it dynamic in two dimensions, you need to
adopt a VBA approach.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______