update source data for plot

[wagnersr]

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

[Mark W.]

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 ]

[wagnersr]

OK, I couldn't use exactly what you proposed, but by learning about OFFSET and defining names, I did exactly what I wanted! Thanks!