It’s quite common in financial reporting to carry over data from one periodic report to its successor – from one month to the next, say. The ‘Current’ data in one report becomes the ‘Previous’ data in the next one; percentage or absolute change might be calculated. Something like this:
The Previous data is brought forward from the predecessor report, and is not to be changed (grey fill); the Current data is entered in this report (green fill).
I thought it would be useful to have a template that (a) helped to set up the data ranges to roll-over, and (b) performed the roll-over, creating a new workbook and copying the data. There’s a worksheet that acts as a control panel:
The parameters are:
- base name for the workbooks (created as <basename>_yymmdd.xls)
- date
- period (as used by the DateAdd function)
- count (in the sequence of reports).
(“rob” stands for “roll-over book”).
The range set up is done via a form:
This allows you to select pairs of ranges, with a given name, defining names of the form Prev_X and Curr_X. The ranges are checked for non-intersection, single area and same row/column dimensions. You can have as many pairs of ranges as necessary. All other cells are as defined in the template (e.g. with formulas).
Clicking the ‘Roll Over’ button generates a new report from the template, incrementing Date and Count, and copying Curr ranges to Prev ranges. You then edit and save as normal.
I’ll talk about the design tomorrow. There are a couple of Class modules in there – any ideas?
2 Responses to “Roll-over Periodic Workbook – Part 1”