Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Taranaki, New Zealand
    Thanked 0 Times in 0 Posts
    Hi there

    I have this spreadsheet with columns and columns of data. The data is transferred into graphs. The columns are 3 years (36 months) but the data I use for my graphs are 13 months. So at the start of a new month I move the pretty coloured lines to the new and correct months.

    What I need is a foruma that calculates the active 13 months. Is there something like that ?? so when you drag the active area down, the sum total changes.

    Hope this is clear

    Thanks heaps

  2. #2
    New Lounger
    Join Date
    Apr 2010
    Thanked 0 Times in 0 Posts
    I'm not really a charts and graphs expert, but your problem intrigued me so I decided to fiddle with it a bit. I got quite frustrated trying to use formulas in the chart data source fields, so I worked it from a different angle. I ended up building a dynamic range on the spreadsheet that automatically updates when the date changes and then building a chart from the dynamic data. The attached spreadsheet shows my take on what you are trying to accomplish. You may need to modify the logic a bit to fit what you are trying to do.

    Some notes regarding the formulas in the spreadsheet:
    - I like to use named ranges in my formulas because it makes it easier to move things around and it makes the formulas look nicer.
    - There are three elements that work together: 1) the data itself, 2) a cell with a formula that gives the address of the location in the data with the most current date, and 3) a dynamic data range that recreates the most recent 13 months of data using formulas that reference the address of the most recent date in the data range.

    I hope the spreadsheet makes sense, and that it helps...



    edit: uploaded slightly modified file.
    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