Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Pensacola, Florida USA
    Thanked 0 Times in 0 Posts

    Question Can updating rolling calendar tables and their graphs be automated?

    I generate several reports each month with trending graphs created from calendar tables. The tables have, for example, three rows for years of twelve columns for the twelve months. Each cell has a numeric value in it and at the end of each row is a calculated average of those numbers. The month columns are arranged with last month in the 12th column.

    The table for the report for October looks like: OCT'11 NOV'11 DEC'11 JAN'12 FEB'12 MAR'12 APR'12 MAY'12 JUN'12 JULv AUG'12 SEP'12 AVG

    The trending graph generated has those 13 points arranged in that order. The report for Nov will have OCT'12 inserted between SEP'12 and AVG so that the calendar is continuously rolling over. I'd like to find a way to automate the process of inserting the next month and deleting the year-old month from the first column. If that could be done then filling the new cells with the proper values could also be automatic from the master data table.

    As long as I'm the only person doing these reports it's not big deal but sooner or later I'm going to have to let someone else do them and teaching people with ZERO excel skills is a nightmare since there are 11 different reports generated each with specific data extracted from the master table.

    Perhaps what I need is some training in creating and using macros but so far I've had no luck with that. Any help anyone might have would be appreciated.

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Redcliff, Alberta, Canada
    Thanked 5 Times in 5 Posts
    Are you familiar with OFFSET formulas? Without seeing your data, this is where I'd start
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Tags for this Thread

Posting Permissions

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