This article will walk you through customising your opening balances to roll over from the current year to the next year. We highly recommend reading Customisations using named ranges and Formulas and Coded Actions prior to attempting to customise your opening balances.


The opening balances code takes values from the relevant ranges and copies and pastes them into the new workpaper when performing the import opening balances function.


There are three main classes of named ranges used in the Opening Balance code:


Fixed List


The fixed list formula copies information from last year's closing balance to this year's opening balance by matching the text descriptions from one period to another. It is intended to be used for lists of data that have unique rows with a lot of data.


The fixed list named ranges look a little different to the other ranges, as it has additional variables. It should be labeled as FixedList.1_2_3.  When there are multiple they should be labelled as:


FixedList_1.1_2_3

FixedList_2.1_2_3 etc.


The string "1_2_3" requires you to input three variables, which specify which columns the opening balance code looks for when rolling over, as follows:
Variable 1: The column in the range which identifies the row which we copy from period to period
Variable 2: The column in the range which represents closing balance data (to roll forward to the new workpaper)
Variable 3: The column in the range which  represents opening balance data (that is rolled forward from the previous workpaper)


We will use the tax reconciliation as an example.  We want the data from the column labelled "This Year" to be rolled over into the "Last Year" column.


You can see the named range is FixedList.1_5_6

Variable 1: Column B, which is the tax reconciliation items. Even though it is not the first column in the worksheet, it is the first column in our named range. It is what will identify the row to roll over to the correct row, even if additional rows have been added.
Variable 2: Column F is the 5th column. This is the current year balance that to be rolled over into column G next year.
Variable 3: Column G is the 6th column, which is last years balance. This is the column we want the information from the prior year's column F to populate in.


Dynamic List


The dynamic list formulas are designed to be used with tables that could have a varying number of rows (or records). The following named ranges are used:


DynamicList: Copies the list from the opening worksheet into the same location in the new worksheet
DynamicCB is a list of closing balance data (to roll forward to the new workpaper)
DynamicOB is a list of opening balance data (that is rolled forward from the previous workpaper)


You cannot have dynamic ranges with overlapping rows.

For example:

Single Cells


Copies the value of a single cell from the opening balance workpaper into a new workpaper.


SingleOB: opening balance value (that is rolled forward from the previous workpaper)

SingleCB: closing balance value (to roll forward to the new workpaper)


For example: