+ Reply to Thread
Results 1 to 4 of 4

How to dynamic chart a rolling three years (each year as separate line onf graph)

  1. #1
    Registered User
    Join Date
    11-15-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    How to dynamic chart a rolling three years (each year as separate line onf graph)

    rolling three year example.xlsx

    I wonder if someone could help with a bit of chart automation, which is beyond me, but Iím sure will be simple for people on this forum.

    If you look at the spreadsheet, itís an example of what Iím trying to do. I have a series of data, which is displayed in a graph as three separate lines, each representing a year.

    What Iím looking to do is get the chart to automatically update when the next monthís data is added, so that the graph shows a rolling three years (each year in a separate line on the graph).

    I hope someone is able to help. Thanks

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,328

    Re: How to dynamic chart a rolling three years (each year as separate line onf graph)

    named ranges

    MY_YEAR1 =OFFSET(MY_YEAR3,-24,0)
    MY_YEAR2 =OFFSET(MY_YEAR3,-12,0)
    MY_YEAR3 =OFFSET(MYLABELS,0,1)
    MYLABELS =OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A)-11,1,12,1)
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to dynamic chart a rolling three years (each year as separate line onf graph)

    Hi, and welcome to the forum.

    See attached which has a second chart in which I've added three dynamic range names for each of the three years and a dynamic range name to pick up the month names.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    11-15-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: How to dynamic chart a rolling three years (each year as separate line onf graph)

    Thanks very much for your feedback. I'll try out each method and let you know how I get on. Much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 07-29-2013, 06:28 AM
  2. [SOLVED] Dynamic rolling chart
    By katy1985 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-13-2013, 03:50 AM
  3. [SOLVED] Simple way of creating rolling year chart
    By katy1985 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-12-2013, 05:14 AM
  4. Replies: 3
    Last Post: 12-30-2010, 07:24 AM
  5. Compare values year to year in a line graph
    By Purdue02 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-04-2005, 02:28 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1