Use of OFFSET function to define X-Axis

  • I am trying to create a chart using this formula for the X-Axis section of the SERIES formula:
    =OFFSET(INDIRECT("Sheet1!"&ADDRESS(Sheet1!$J$2,3)),0,0,Sheet1!$K$2,1)

    • Column C on my worksheet contains the week's worth of X-axis points
    • $J$2 contains the row number of the first X-value for this particular chart (it's one of 8 to be drawn from the same data)
    • $K$2 contains the number of rows to include in this particular chart's X-Axis

    If I enter this formula into a cell, it will evaluate to a 1-row array with the correct values.
    If I use this function in a SERIES function, I get an error message that says: "that function is not valid."


    I would really appreciate it if someone could explain why my formula isn't working, and how to correct it.


    I went back to some earlier projects where I'd successfully used an OFFSET function to define the X-Axis. I noticed that none of them used a formula for the first parameter of OFFSET, and none included an INDIRECT function or an ADDRESS function. Could one of these 3 differences explain things?


    Thanks for your help!

  • Hi,


    The best solution for your SERIES formula is to create a Named Range ... and then use this name in your SERIES formula ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,


    Your named range should be defined as per your initial message


    Code
    1. =OFFSET(INDIRECT("Sheet1!"&ADDRESS(Sheet1!$J$2,3)),0,0,Sheet1!$K$2,1)


    Then, the name you have assigned should be used in your SERIES formula.


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)