On 2002-02-20 07:18, wagnersr wrote:
I have two columns of data (x,y) that I want to graph. However, I only want to plot a certain range of these values. That's easy enough, but the problem is that those two columns are going to change as I alter some reference cells. Because of that, the range I want to graph moves up and down within the columns. So, every time I tweak one reference cell, I have to manually go through and find the cell that now contains the value I want to start my plot with. I must be able to do this with a combination of Match, Index, Lookup, etc, but the answer has evaded me so far. Any suggestions?
-steve
Not quite sure what your "reference" cells
contain, but consider the following...
Suppose A1:B6 contains...
{"X","Y"
;1,10
;2,20
;3,30
;4,40
;5,50}
...and that you've graphed this data using
this chart SERIES() function...
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,1)
Instead, you could make your reference cells D1 (containing the starting value) and E1 (containing the number of values to be graphed). First, create a defined name, Sheet1!range1, with the following reference...
=OFFSET(Sheet1!$A$1,Sheet1!$D$1,,Sheet1!$E$1)
Next, create a defined name, Sheet1!range2, with the following reference...
=OFFSET(Sheet1!range1,,1)
Finally, change your chart's SERIES()function to...
=SERIES(Sheet1!$B$1,Sheet1!range1,Sheet1!range2,1)
...and you should be "good to go".
This message was edited by Mark W. on 2002-02-20 07:45