Results 1 to 2 of 2
2012-10-04, 22:25 #1
- Join Date
- Dec 2009
- Pensacola, Florida USA
- Thanked 0 Times in 0 Posts
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.
2012-10-11, 00:15 #2
- 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