1. Row 1
Cells in row 1 from column A to Z are to contain headings. More explanation on each heading are in chapter 'Explanation for the sample structure column by column'.
The headings (or 'data titles') are :
In cell A1: Write SRC (for SOURCE)
In cell B1: Write DATE
In cell C1: Write MTH (for MONTH)
In cell D1: Write SRC NO (for SOURCE NUMBER)
In cell E1: Write SRC NO 2 (for OTHER REFERENCE or Additional REFERENCE NUMBER)
In cell F1: Write NOTE
In cell G1: Write FC (for Foreign Currency)
In cell H1: Write RATE (for Rate of Currency Exchange)
In cell I1: Write CONVERTED (to RM)
In cell J1: Write ACCOUNTS
In cell K1: Write CHECK ACCOUNTS
In cell L1: Write RM-DEBIT/(CREDIT)
In cell M1: Write CUM. AMOUNTS (for Cumulative Amounts)
In cell N1: Write BANK RECON (for BANK RECONCILIATION).
In cell 01: Write DEBTOR INV. STATUS (for DEBTOR Invoice Status).
In cell P1: Write DEBT AGE (days)
In cell Q1: Write DEBT AGE GROUP
In cell R1: Write DEBT TERM
In cell S1: Write DEBT DUE DATE
In cell T1: Write CREDITOR INV. STATUS (for CREDITOR Invoice Status).
In cell U1: Write CREDIT AGE (days)
In cell V1: Write CREDIT AGE GROUP
In cell W1: Write CREDIT TERM
In cell X1: Write CREDIT DUE DATE
In cell Y1: Write BS or PL
In cell Z1: Write ORIGINAL ORDER.
In cell AA1: Leave blank (It serves as right-side border)
The headings are used to indicate what type of data should be put in cells under each title heading.
Freeze row 1:
You may want to freeze row 1 so that you can still see the data titles in row 4 when you scroll down.
2. Row 2
Rows for transaction data starts at row 2. This is where you can start to input raw data (the first row for data). Data must start at this row. Data ends at colmn Z.
3. Range A2:Z7 : Initial Data Area
Initially, range A2:Z7 is the beginning (initial) data area, before any new row is added into this range. This range is dynamic ie as it will vertically expand or contract as we add more row/s or delete row/s in this area.
Data area
Data area is a rectangular area (bounded by a blank row on top, a blank column on the right, a blank row at the bottom and a filled column in the left) which is expected to contain the data. In this book, the data area starts at cell A2 and nitially ends at cell Z7.
4. Row 8
Make the row immediately below the Data Area as the bottom-side border. Initially, in this book, the bottom-side border is row 8. All cells in row 8 initially should be left blank. This row serves as a bottom-border for the data area (rows of data which initially encompasses row 2 to 7). If any cell in this row is not blank, some operation will not perform correctly, particularly data filtering and PivotTable functions. We can use data/validation function to help us prevent entry of into the cells. (Refer to Topic 28 on 'Tips And Shortcuts'). Colour its cells as visual indicator.
The bottom-side border row is also dynamic as it will move downward or upward as we add more rows of data or delete a row above it.
5. Row 9 : Total row
Designate a row immediately below the bottom-side border as Total Row as we will enter some totalling in certain cells (not in all cells) in this row. Initially, in this book, the Total Row is row 9. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side border row.
In cell A9, type:
TOTAL/SUBTOTAL
This is to indicate that some of the other cells in row 9 are used to accommodate formulas which calculate totals/subtotals.
6. Row 10 : Checking row
Designate a row immediately below the Total Row as Checking Row as we will enter some formulas in certain cells (not in all cells) in this row for checking purpose. Initially, in this book, the Checkng Row is row 10. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
This is to indicate that some of the cells in row 10 are used to accommodate formulas that will check the logic of the results of the other formulas in our file.
For example, cell J10 contains a formula which checks the result of the formulas in cell H10 and J10. The sum of cell H10 and cell J10 should be zero (total Debit amounts minus Total credit amount). The formula is:
=IF(H1+J1=0, "Ok", "Please check")
This formula will show OK if the sum is 0 (zero). Otherwise, it will show "Please Check" to indicate to us to check something. You can read about the IF function in Topic 29.
8. Row 11 : Cut-off-date row
Designate a row immediately below the Checking Row as Cut-Off Date row, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. Initially, in this book, the Cut-off-Date row is row 11.This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
9. Row 12 : In the sample, we put a label Last Row Data in cell A12. The row having this label is immediately below the Cut-Off row. This label is to indicate that we have a formula in cell C12 that will show the last row in the Data Area. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row.
10. Row 30000
This is alternative to value n cell A12. This is the expected last row for the data area. This row number will appear in many formulas. Why 30000? Because the author expects that rows up to this number is sufficient to hold data for one year. Of course, if you find this row number insufficient, you can increase the number in every formula. Similarly, if your find that row 10000 is enough for your needs, you can change the 30000 in each affected formula to 10000.
To easily find and change every number or character (30000 in this case) in a formula, just use menu Edit/Replace (Refer to topic 27 on 'Shortcuts And Techniques For Working Better And Faster').
Why 300000? Because the author expects that rows up to this number is sufficient to hold data for one year. 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 Edit -> Replace function. Similarly, if your find that row 10000 is enough for you, you can change every formula to 10000.
Dynamic rows
To recap, the following rows are dynamic (it will move downward or upward as we add more rows of data or delete a row above bottom-side-border row) :
BottomSideBorderDATA
TotalRow
CheckingRow
CutOffDateRow
LastRowDATA
Data Area
Sample formulas in use in sheet Data'
| Cell | Formula |
| J2 | =ROUND(G2*H2,2) |
| K2 | =IF(ISERROR(VLOOKUP(J2,INDIRECT("TB!A2:A"&$Z$1),1,FALSE())),"NOT OK","OK") |
| M2 | =IF(J2=J1,M1+L2,L2) |
| P2 | =IF(O2="open",CutOffDate-B2,"NR") |
| Q2 | =IF(O2="open",IF(P2<30,"0-30",IF(P2<60,"30-60",IF(P2<90,"60-90",IF(P2<120,"90-120",">120")))),"NR") |
| Z1 | =ROW(LastRowTB) |
| G9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| J9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| M9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| V9 | =SUBTOTAL(9,INDIRECT(ADDRESS(2,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))) |
| C12 | =ROW(LastRowDATA) |
Certain columns need to be formatted with suitable formatting
| Column | Heading | Format |
No comments:
Post a Comment