Thanks:  0
Likes:  0

# Thread: update source data for plot

1. 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

2. 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 ]

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•