Monday, May 30, 2011

Getting ledger details of an account Explanation pg 195 196 197 19 199 200

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.

2. Next, we will set the combo box to that when we click on it, a list will appear and we can select an item from the list. In this case, we want to select an account from it. This requires us to have a sheet which contain a list of accounts. This is in sheet TB and the list is in first column in the range A2 to A2000. We want to make the list of accounts in sheet TB appear in the combo box. Here is how:

Right-click on the combo box. A floating menu will appear. Select Format Control to open pen the Format control dialog box. Click on the tab Control. In the box on the right of label Input Range, enter the address where the list of customers is located (in this case, it is TB!A2:A2000).

Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Input Range setting to take effect. Now if you click on the combo, a list will appear and you can click on one item from the list to select it. The list will disappear and the selected item will appear in the combo.

197

3. In cell C4, write this formula: =INDEX(TB!A2:A2000, C3). This formula will retrieve the name of the account we selected from the combo, not from the combo itself but by searching through the list in sheet TB in range A2:A2000 and by relying on the position that appear in cell C3.

4. In cell F4, write this formula: =F7+M7. Later we will know that cell F7 contains the sum of debit amounts whilst cell M7 contains the sum of credit amounts, so the formula will get the net of both amounts.

5. In cell C6, write DEBIT ENTRIES. This serves as a label.
6. In cell J6, write CREDIT ENTRIES. This serves as a label.
7. In cell C7, write TOTAL. This serves as a label.
8. In cell F7, write this formula: =SUM(F9:F50).
9. In cell J7, write TOTAL. This serves as a label.
10. In cell F7, write this formula: =SUM(M9:M50).
11. In cell B8, write 1. This will be used by formulas in cells A9 and B9. It indicates the starting row from which the formulas search for an account name.
198
12. In cell C8, write SRC. This serves as a label.
13. In cell D8, write DATE. This serves as a label.
14. In cell E8, write SRC NO. This serves as a label.
15. In cell F8, write RM-DEBIT. This serves as a label.
16. In cell 18, write 1. This will be used by formulas in cells H9 and 19. It indicates the starting row from which the formulas search for an account name.
17. In cell J8, write SRC. This serves as a label.
18. In cell K8, write DATE. This serves as a label.
19. In cell L8, write SRC NO. This serves as a label.
20. In cell M8, write RM-CREDIT. This serves as a label.
21. In cell A9, write this formula:
=MATCH(INDIRECT("C4"), INDIRECT("DATA!G"&B8+1&": $G30000"),0)

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

22. In cell B9, write this formula:
B8+49.
This formula returns the real row position (relative to the sheet) of the found account name. This row position is used by formulas in subsequent columns.

23. In cell C9, write this formula:
=INDIRECT("DATA!A"&B9)
This formula will extract the source from column A in sheet DATA in row as stated in cell B9. This would be the source of the first occurrence of the account name.

199

24. In cell D9, write this formula:
=INDIRECT("DATA!B"&B9)
This formula will extract the date from column B and row as stated in cell B9 in sheet DATA. This would be the date of the first occurrence of the account name.

25. In cell E9, write this formula:
=INDIRECT("DATA!D"&B9)
This formula will extract the source number from column D and row as stated in cell B9 from sheet DATA. This would be the source number of the first occurrence of the account name.

26. In cell F9, write this formula:
=INDIRECT("DATA!H"&B9)
This formula will extract the RM-DEBIT value from column H and row as stated in cell B9 from sheet DATA. This would be the transaction value of the first occurrence of the account name.

27. In cell H9, write this formula:
=MATCH(INDIRECT("C4"), INDIRECT("DATA!I"&18+1&": $130000"),0)
This formula will find the first occurrence of account name available in cell C4 from column I in the data area in sheet DATA starting from row as stated in cell 18 to row 30000. If it finds one, it will return the row position of the account name relative to the range being searched. If there are no matches, it will return #NA.

28. In cell 19, write this formula:
=18+H9
This formula returns the real row position (relative to the sheet) of the found account name in column I ('ACCOUNT CREDIT). This row position is used by formulas in subsequent columns.

29. In cell J9, write this formula: =INDIRECT("DATA!A"&19) This formula will extract the source from column A in sheet DATA in row as stated in cell 19. This would be the source of the first occurrence of the account name in column ACCOUNT-CREDIT.

30. In cell K9, write this formula: =INDIRECT("DATA!B"&19) This formula will extract the date from column B and row as stated in cell 19 in sheet DATA. This would be the date of the first occurrence of the account name in column 'ACCOUNT-CREDIT'.

200

31. In cell L9, write this formula:
=INDIRECT("DATA!D"&19)
This formula will extract the source number from column D and the row is stated in cell 19 from sheet DATA. This would be the source number of the first occurrence of the account name in column ACCOUNT-CREDIT.

32. In cell M9, write this formula:
=INDIRECT("DATA! J"&19)
This formula will extract value from column RM-CREDIT from column J and the row is stated in cell 19 from sheet DATA. This would be the credit transaction value of the first occurrence of the account name in column ACCOUNT-CREDIT.

33. Copy down formulas in cells A9 to M9. The formulas will get the rows containing details of account as stated in cell C4. If you see an #NA in a row, it means there is no more rows containing the account name from sheet DATA.

NOTE

If you don't want to print anything in column A and B, you can hide the column 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 ledger to follow your own structure.

TIP

You can consolidate Credit entry details (in this case, in columns H to M) to appear below the Debit entry details (in this example, in columns A to F) by copying all values (not the formulas) into a separate sheet, and then sort the details by date. This will cause the data to appear in chronological order.

You can also have Credit entries appear below the Debit entries (or vice-versa) in the same column by combining the formulas:
=IF(MATCH(INDIRECT("C4"), INDIRECT("DATA!G"&B8+18": $G30000"),0))=#NA, MATCH(INDIRECT("C4"), INDIRECT("DATA!I"&18+18": $130000"),0)),"")




No comments:

Post a Comment