Statement of accounts Explanation pg 188 189 190

Here is a quick explanation of the structure (row by row):

1. Notice the box with a little downward-pointing arrowhead. This is called a Combo Box. Here is how to put a combo box into a sheet:

Click menu View/Toolbars/Form.
A floating toolbar (Form Toolbar) will appear.
Look for the Combo Box tool (sometimes called the 'Drop-down' or 'Drop-Down Combo') and click on it once.
Point your mouse to where you want to put the combo box. Click on the location once. A combo box will appear there.

TIP

You can resize the combo box by clicking on it once and then dragging on a handle that appears. You can relocate the combo box by dragging it.

You can dismiss or close the Form Toolbar for now, so not to clutter your sheet.

3. The formulas in cells C8 to C12 expects that the list of customers in sheet CUSTOMERS is structured as below (i.e. titles in row 1, data starts from row 2 in column B to column G):

	A	B			C			D			E			F			G
1		NAME		FULL NAME	ADDRESS-1	ADDRESS-2	ADDRESS-3	ADDRESS-4	
2		CUSTOMER A	DOL FATAH	A20			JALAN 5		MUAR		JOHOR
3		CUSTOMER B	ABU DAUD	A21			JALAN 6		MANTIN		NEGRI
4		CUSTOMER C	ALI GOOCHI	A22			JALAN 7		KLUANG		JOHOR
6		CUSTOMER D	SITI NOOR	A23			JALAN 8		JASIN		MELAKA
6		CUSTOMER E	MIN TUTU	A24			JALAN 9		GOMBAK		SELANGOR
7

4. In cell A2, write this formula:
=INDEX(CUSTOMERS!B2:B100,A1)

This formula will retrieve the name of the customer we selected from the combo, not from the combo itself but by searching through the list in sheet CUSTOMERS in range B2:B100 and by relying on the position that appear in cell A1.

5. In cell C8, write this formula:
=VLOOKUP(A2, Customers!B1:G100,2,FALSE)

This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and then return the item in the 2nd column of the list (which is the full name).

191

6. In cell C9, write this formula:
=VLOOKUP(A2, Customers!B1:G100,3,FALSE)

This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and then return the item in the 3rd column in the list (which is the 1st part of the address).

7. In cell C10, write this formula:
=VLOOKUP(A2, Customers!B1:G100,4,FALSE)

This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 4th column in the list (which is the 2nd part of the address).

8. In cell C11, write this formula:
=VLOOKUP(A2, Customers!B1: G100,5,FALSE)

This formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 5th column in the list (which is the 3rd part of the address).

9. This In cell C12, write this formula:
=VLOOKUP(A2, Customers!B1: G100,6, FALSE) formula will search the 1st column (by default) in the list range for the short name that appears in cell A2 and return the item in the 6th column in the list (which is the 4th part of the address).

10. In cell C14, write TOTAL.

11. In cell G14, write a formula to sum values of open (unpaid) invoices in cells G17 downward.

12. In cell B16, write the figure 1. This number will be used in the formula in cells A17 and B17. This actually indicates the row number where the search for unpaid invoices starts.

13. In cell C16, write Date. This serves as a label.

14. In cell D16, write Inv No. This serves as a label.

15. In cell E16, write RM. This serves as a label.
16. In cell F16, write Status. This serves as a label.
17. In cell G16, write Open only. This serves as a label.
192
18. In cell A17, write this formula:
=MATCH($A$2, INDIRECT("DATA10"&816+1&":$030000"),0)

This formula will find the first occurrence of the customer name available in cell A2 from column G in the data area in sheet DATA starting from row as stated in cell B16 to row 30000. If it finds one, it will return the row position of the customer name relative to the range being searched. If there are no matches, it will return #NA.

19. In cell B17, write this formula:
=B16+B17

This formula returns the real row position (relative to the sheet) of the found customer name. This row position is used by the formula in subsequent columns.

20. In cell C17, write this formula:
=INDIRECT("DATA!B"&B17)

This formula will extract the date from column B in sheet DATA in row as stated in cell B17. This would be the date of the first occurrence of the customer name.

21. In cell D17, write this formula:
=INDIRECT("DATAID"&B17)
This formula will extract the Invoice Number from column D and row as stated in cell B17 in sheet DATA. This would be the Invoice Number of the first occurrence of the customer name.

22. In cell E17, write this formula:
=INDIRECT("DATA!H"&B17)
This formula will extract the RM value from column H and row as stated in cell B17 from sheet DATA. This would be the RM value of the first occurrence of the customer name.

23. In cell F17, write this formula:
=INDIRECT("DATA!N"&B17). This formula will extract the Status from column N and row as stated in cell B17 from sheet DATA. This would be the invoice status (whether paid or unpaid) of the first occurrence of the customer name.

24. In cell G17, write this formula:
=IF(F17="pd",".", E17)

This formula will extract the RM in cell E17 if it has not been paid (by relying on cell F17). If already paid, it will return "-".

24.

Copy down formulas in cells A17 to G17. The formulas will get the rows containing details of unpaid invoices to customer stated in cell A2. If you see a #NA in a row, it means there are no more rows containing the customer name.

NOTE

If you do not want to print anything in column A and B, you can hide the columns before printing. Or, you can set the Print Area (Use menu File/Print Area/Set Print Area) before you print.

If you understand the mechanics of the formula and functions, you can change the design of the statement to follow your own structure.