Explanation for structure in sheet TB

Row 1

In cell Al, type this label: TOTAL/SUBTOTAL

This is to indicate that some of the cells in row 1 are used to accommodate formulas which calculate totals/subtotals.

In cell F1, write this formula: =SUBTOTAL(9, INDIRECT("F5:F2000"))

This formula calculates the total of the amounts in cells in column F (from row 5 to row 2000). We will see later that cells F5 to F2000 will contain totals of debit amounts for each account in column A.

In cell G1 write this formula: =SUBTOTAL(9, INDIRECT("G5:G2000"))

This formula calculates the total of amounts in cells in column G (from row 5 to row 2000). We will see later that cells G5 to G2000 will contain totals of credit amounts for each account in column A.

In cell H1 write this formula: =SUBTOTAL(9, INDIRECT("H5: H2000"))

This formula calculates the total of the amounts in cells in column H (from row 5 to row 2000). We will see later that cells H5 to H2000 will contain net totals of credit and debit amounts for each account in column A.

Other cells: You can use other cells in row 1 for other purpose.

Change of formulas: You can change the formulas if you know the effect.

15

Why row 5 to 20007

It is expected that row 5 to row 2000 is sufficient to accommodate all accounts names. If you find that you need more rows, then change the 2000 in each affected formula above to the required figure.

Row 2

In coll A2, type Checking. This is to indicate that some of the cells in row 2 are used to accommodate formulas that will check the logic of the result of the other formulas in our file.

For example, cell H2 contains a formula which checks the results of the formula in cell G1 and H1. The sum of cell G1 and H1 should be zero (total Debit amounts minus Total credit amount). The formula is:

=IF(61-81-0, "Ok", "Please check")

This formula will show OK if the sum is zero. Otherwise, it will show Please Check to indicate to us to check the data.

You can use other cells in row 2 for other purpose.

Row 3

All cells in row 3 should be left blank. This row serves as a border for rows of data (start at row 4 to 2000). If any cell is not blank, some operation will not perform correctly, particularly the data filtering and PivotTable function.

Row 4

Cells in row 4 are used to show the data titles (or field titles or column titles) for columns A to I. The data titles are used to indicate what data should be put in cells under each title.

16

In cell A4, write ACCOUNTS.
In cell 84, write BALANCE SHEET MAIN CATEGORY.
In cell C4, write BALANCE SHEET SUB CATEGORY.
In cell D4, write PL CATEGORY (for PROFIT & LOSS CATEGORY).
In cell E4, write PL DETAIL (for PROFIT & LOSS DETAIL).
In cell F4, write RM DEBIT (for TOTAL RM DEBIT).
In cell G4, write RM CREDIT (for TOTAL RN CREDIT).
In cell H4, write RM NET (for NET OF DEBIT AND CREDIT).
In cell 14, write CHECK DUPLICATE.

In cell J4 onwards, do not write anything unless you know the effect. Do not rearrange the order unless you know the effect. Do not change the spelling unless you know the effect.

Row 5

This is where you can start to input your accounts names (the first row for data).

Some cells in row 5 will contain initial formulas. Later on, when you add data into rows below it, just copy the formula down. Be careful not to delete the cells containing formula.

In cell F5, write this formula:

=SUMIF(INDIRECT("DATA!G5: G30000"), A5, INDIRECT("DATA! H5: H30000"))

This formula computes the total of the debit amounts of the account in column H in sheet DATA where its name in column G in that sheet is the same as the one in cell A5 in sheet TB.

In cell G5, write this formula:

=SUMIF(INDIRECT("DATA! 15: 130000"), AS, INDIRECT("DATA!J5: J30000"))

This formula totals the credit amounts of the account in column J in sheet DATA where its name in column I in that sheet is the same as the one in cell A5 in sheet TB.

17

In cell H5, write this formula:

=F5+G5

This formula computes the net of the total the credit amounts and the total of debit amounts of the account.

In cell 15, write this formula:

=IF(COUNTIF(INDIRECT("A5: A2000"), A5)>1, "DUPLICATE", "OK")

This formula indicates whether the account name in column A is duplicated. If it is a duplicate, it will return DUPLICATE, otherwise OK.

Freeze row 5:

You may want to freeze this row 5 so that you can still see the titles in row 4 when you scroll down.

Row 2000

This is the expected last row for the data area. This row number will appear in many formulas in sheet TB.

Why 2000? The author expects this number of rows to be sufficient to hold all accounts names. Of course, if you find this number insufficient, you can increase the number in every formula. To easily change every number or character in a formula, just use the menu Edit/Replace. Similarly, if your find that row 500 is enough for you, you can change every formula to 500, etc.

Formatting

Certain columns need to be formatted with appropriate format to accommodate proper type of data. Usually, data can be a 'text' type, 'date' type or 'number' type. Columns to accommodate 'date' type must be formatted with 'date' formatting. Columns to accommodate 'number' type must be formatted with 'number' formatting. Columns to accommodate 'text' type must be formatted with 'text' formatting.

18