Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rolling Graph (XP)

    I have 75 weeks of data that I show on a graph. The graph shows only 52 weeks at a time. Every week when I add new data, I am currently manually going into the chart and changing the range cells to increment the "first" week of on the chart by one and incrementing the upper range (the last week) by one, as well as changing the X-axis range. Can this be "automated"?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rolling Graph (XP)

    If you attach a stripped copy of your workbook, we could fiddle in a working example.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Rolling Graph (XP)

    Try this as an example

    The chart plots the named ranges yValue vs xDate

    yValue and xDate always contain 52 data points. The starting points is 52 points from the end.

    I created the named ranges:
    CountValues =COUNTA(Sheet1!$B:$[img]/forums/images/smilies/cool.gif[/img]
    This counts the values in column B

    xDate =OFFSET(Sheet1!$A$1,CountValues-52,0,52,1)
    yValue =OFFSET(Sheet1!$A$1,CountValues-52,1,52,1)

    You can place as many dates in A as you want. The ranges are based on how many points there are in column B. It is important for this method that there be no Blanks in B, though "=na()"s are acceptable

    Hope this gets you started,
    Steve
    Attached Files Attached Files

Posting Permissions

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