Defining Excel Trial Balance Templates
To define an Excel trial-balance template, you define the first row of the named region, which contains the metadata tags. Other information may be defined outside of the region, but the system only considers what is within the named region. For example, the template includes a title and an amount summary. These are outside the named region and are not processed when loading data.
To load data using a template, the system uses a named range definition to find the dimensions and the related data. For the trial balance template, the predefined range is called upsTB, and it can be seen using the "Name Manager" option in Excel.
The following template contains one line of metadata (row 1) and three lines of imported data (rows 5–7).
Dimension Values and Amount should be populated in the respective columns as per the Tags defined in row 1. To add additional dimension tags, add columns. Add data by adding rows.
When adding rows or columns, add them within the named region. Excel updates the region definition automatically. If you add rows outside of the region, update the region to include these new rows or columns. When adding dimension columns, add a dimension tag to specify when the column is an account, entity, intercompany transaction, amount or user defined (UD) dimension. Note that the entity dimension is represented by the tag for "Center."
Table 4-16 Data Management dimension tags and the corresponding tags
| Data Management Dimension | Valid Tags |
|---|---|
|
Account (Required) |
A, Account, SrcAcctKey |
|
Center (Required) |
C, Center, SrcCenterKey |
|
Description (Optional) |
D, Description, SrcAcctDesc |
|
IC Counter Party (Optional) |
I, IC, ICCoParty |
| User Defined 1-User Defined 20 (Optional) | 1-20, UD1-UD20, UserDefined1-UserDefined20 |
| Amount (Required) | V, Amount, SrcAmount |
In the template that is provided with Data Management, some of the rows are hidden. To update the columns and the column tags, you need to unhide these rows. To do this, select the row above and below the hidden rows, and then update the cell height. A setting of 12.75 is the standard height for cells, which shows all hidden rows for the selected range in the sheet. You can re-hide the rows after changes have been made.
