2. Data entry structure

Where to enter data

1. Use a dedicated sheet.
2. Name the sheet appropriately. In this book, its name is "Data". This name is used in formulas in this book.

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)

	K	L	M	N	O
1	ACCOUNTS	Amounts	Cum. Amounts	Check Accounts	Bank Recon.
2				(formula)
3
4
5
6
7
8
9				(formula)
10
11
12

Explanation for 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 row is also dynamic as it will move downward or upward as we add more rows of data or delete a row above it.
8. 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. 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.
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 above bottom-side-border 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 above bottom-side-border 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 above bottom-side-border 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 above bottom-side-border row) :

BottomBorderDATA
SubtotalRow
CheckingRow
CutOffDateRow
LastRowDATA

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
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

Changing the structure

You can design the structure differently as you wish.