#### Sample structure

A | B | C | D | E | F | G | H | I | J | |

1 | SRC | DATE | MTH | SRC. NO. | 2ND SRC. NO. | Note 1 | Note 2 | Note 3 | Note 4 | Note 5 |

2 | ||||||||||

3 | ||||||||||

4 | ||||||||||

5 | ||||||||||

6 | ||||||||||

7 | ||||||||||

8 | ||||||||||

9 | Subtotal | |||||||||

10 | Checking | |||||||||

11 | Cut-off Date | (date) | ||||||||

12 | LastRowDATA | (formula) |

(formula)

K | L | M | N | O | P | |

1 | ACCOUNTS | Amounts | Cum. Amounts | Check Accounts | Bank Recon. | Sales Inv. Status |

2 | (formula) | |||||

3 | ||||||

4 | ||||||

5 | ||||||

6 | ||||||

7 | ||||||

8 | ||||||

9 | (formula) | |||||

10 | ||||||

11 | ||||||

12 |

Q | R | S | T | U | V | W | |

1 | Debt Age | Debt Age Group | Supplier Inv. Status | Credit Age | Credit Age Group | Due Date | Items |

2 | (formula) | (formula) | (formula) | (formula) | |||

3 | |||||||

4 | |||||||

5 | |||||||

6 | |||||||

7 | |||||||

8 | |||||||

9 | |||||||

10 | |||||||

11 | |||||||

12 |

X | Y | Z | AA | AB | AC | AD | |

1 | Qty | Unit of Measure | Price | BS or PL | Original Order | ||

2 | |||||||

3 | |||||||

4 | |||||||

5 | |||||||

6 | |||||||

7 | |||||||

8 | |||||||

9 | |||||||

10 | |||||||

11 | |||||||

12 |

#### Explaination on the sample structure

1. Cells in row 1 from A1 to N1 are to contain

**headings**.

2. Column O serves as the

**right-side border**. Colour its cells as visual indicator.

3. Rows for transaction data starts at

**row 2**.

4. Let the range A2:N7 as the initial

**Data Area**. The Data Area is

**dynamic**as it will expand or contract as we add more row/s or delete row/si.

5. Make the row immediately below the Data Area as

**the bottom-side border**. Colour its cells as visual indicator.

7. The bottom-side border is also dynamic as it will move downward or upward as we add more rows of data or delete a row.

8. Designate a row immediately below the borrom-side border as

**Total Row**as we will enter some totalling in certain cells (not in all cells) in this row. This Total Row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.

9. 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. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.

10. Designate a row immediately below the Checking Row as

**Cut-Off row**, we will enter a cut-off date in a certain cell (not in all cells) in this row for identifying cut-off date. This row is also dynamic, it will move downward or upward as we add more rows of data or delete a row.

11. In the sample, we put a label

**Last Row Data**in cell A13. The row having this label is immediately below the

**Cut-Off row**. This label is to indicate that we have a formula in cell C13 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.

#### 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) :BotomBorderDATA

SubtotalRow

CheckingRow

CutOffDateRow

LastRowDATA

#### Sample formulas in use

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 |

A | Source | Text |

B | Date | Date (eg dd-mmm-yyyy) |

C | Month | Text |

D | Source No. | Text |

E | Other source and no. | Text |

F | Note 1 | Text |

G | Note 2 | Text |

H | Note 3 | Text |

I | Note 4 | Text |

J | Accounts | Text |

K | Checking Accounts | Text |

L | Amounts DR/CR | Number |

M | Cumulative Amounts | Number |

N | Bank Reconciliation marking | Text |

O | Sales Invoice Status | Text |

P | Debt Age | Number |

Q | Debt Age Group | Number |

R | Debt/Credit Term | Text |

S | Due date | Date |

T | Item purchased/Sold | Text |

U | Quantity Purchased/Sold | Number |

V | Unit of measurement | Text |

W | Price | Number |

X | BS or PL marking | Text |

Y | Original order | Number |