Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: update source data for plot

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    New Member
    Join Date
    Feb 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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