Trailing twelve-month report
Posted on 2013-01-31
I have an Excel file that calculates sales reports and compensation. There is a data worksheet that is updated monthly. The worksheet needs to calculate each month's results with a twelve-month look-back.that data.
Ideally, each new month would be added automatically, dropping off the 13th prior month so that the calculation worksheet always displays 12 months (although the data worksheet will "grow." If the old calculation rows became "Hidden" when a new row was added, it would theoretically be possible to run the report for any period.
I know it is possible to use an Offset function (command) to get the calculation (although I don't really know how to do that). But I don't know how to add rows (copy, paste/insert, ??)
I know enough about Excel and VBA to be dangerous but not enough to be able to do what I want to do.
Can anyone help?