Results 1 to 13 of 13
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset formula for chart (Excel 2003)

    I want to use a single chart (bar or line) to dynamically display 1 set of data from a table. The user selects the # of years (1-5) they want to view and the chart should only display those columns of data. I tried to use OFFSET formula but got stuck. Attached is my sample.

    Year=3 (selectable by user)
    Data set 1 = n1
    Data set 2 = n1 n2
    Data set 3 = n1 n2 n3
    Data set 4 = n1 n2 n3 n4
    Data set 5 = n1 n2 n3 n4 n5

    In this case the chart would display 'data set 3' which represents Year 3.

    Thnx, Deborah
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset formula for chart (Excel 2003)

    You can create a defined name ChartData that refers to

    =OFFSET(Sheet1!$C$7,0,0,1,Sheet1!$C$4)

    Select a chart, then select Chart | Source Data.
    Activate the Series tab, then set the Values reference to =SingleDataset.xls!ChartData.
    Click OK.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula for chart (Excel 2003)

    I would use a dummy row of data to graph against (please see attached)
    I created the dummy row directly under you original data and then used this formula =IF(COLUMN()-2<=$C$4,C7,"") to get the data points. The graphs backgrounds correspond to the rows' whose data they are using.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula for chart (Excel 2003)

    Is there a reason why this technique would not work for Excel 2007. I have that version (which I do not like) now on one of my computers - and cannot get the offset formula to work. I keep getting a "That function is not valid" or "The formula you typed contains an error..." message. I've triple-checked the syntax (and even pasted in Hans' as a quadruple check).

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula for chart (Excel 2003)

    Hans beat me to the other option. I wanted to add the one caveat of using the defined name version. Using this method will make the Y axis will be dynamic as well. Unlike the X axis, you cannot specify a min and max value for the Y axis (or at least I couldn't find a way).
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset formula for chart (Excel 2003)

    Does the attached version work in Excel 2007?
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula for chart (Excel 2003)

    Yes - it does work. And I see how it's setup. And I can even duplicate it on my 2003 machine and then open it on my 2007 machine. But still cannot figure out how to create it on the 2007 version.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset formula for chart (Excel 2003)

    I'm sorry, I can't help you with that; I don't have Excel 2007 yet.

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula for chart (Excel 2003)

    I finally tried it with the filename prefixed to the range

    ='5-744343-SingleDataSet.xls'!ChartRangeData

    And that worked. Did not realize that the filename had to be used - especially if only one workbook is open. Sorry for the wild goose chase.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset formula for chart (Excel 2003)

    Ah yes, that's confusing. It usually works too if you prefix the defined name with the name of the worksheet:

    =Sheet1!ChartRangeData

    Excel will automatically substitute the workbook name when you click OK.

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula for chart (Excel 2003)

    Excellent works like a charm - of course. I always struggle with Offset. At first I didn't include the worksheet name in the defined name, I didn't remember having to do that before.

    // Thnx, Deborah <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Offset formula for chart (Excel 2003)

    FWIW, there seem to be a few issues with charts in 2007 if you use a defined name that starts with the word 'Chart', so it seems to be best to use another name, like ChtData instead.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset formula for chart (Excel 2003)

    Thanks for the tip. I am having lots of issues transitioning to 2007: pivot tables; external connections; AND charts.

Posting Permissions

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