New accounting year item 16 Method 2 pg 165 166

Method 2: Use AdvanceFilter

We can use an AdvanceFilter operation to identify the petty cash amounts. We use AdvanceFilter because we want to find rows based on 2 columns (ie ACCOUNT-DEBIT and ACCOUNT-CREDIT).

1. To use an AdvanceFilter operation, we need to set the criteria in another sheet. The criteria may look like the following:

	A			B
1	ACCOUNT-DEBIT		ACCOUNT-CREDIT
2	PETTY CASH A
3				PETTY CASH A
4

The above criteria means that we want to select rows which contain item 'PETTY CASH A' under column ACCOUNT-DEBIT but the same row can contain any item under column ACCOUNT CREDIT, and select also rows which contain item 'PETTY CASH A' under column ACCOUNT CREDIT but the same row can contain any item under column ACCOUNT-DEBIT.

2. After setting the criteria, go to sheet DATA.

3. At sheet DATA, make sure the active cell is a non-blank cell in the data area.

4. Click menu Data/Filter/Advance Filter to open the Advance Filter dialog box.

5. The List range box is automatically filled with the address of the data area. Check that it is correct, otherwise you have to correct your data area.

165

6. Fill the Criteria range box with the address of the criteria you set above. (To easily fill the box with the address, follow these steps: Make sure the insertion bar is already in the box. Click on the tab of the sheet where the criteria reside. Highlight the criteria block. You will notice that as you highlight the criteria block, the Criteria Range box is automatically filled with the address of the criteria block).

7. Click OK.

8. Now you should see only rows containing transactions of the petty cash account you set in the criteria block.

9. Zerorise amounts in column RM-DEBIT which the corresponding item in column ACCOUNT-DEBIT is the petty cash account, and zerorise also amounts in RM-CREDIT where the corresponding items in column ACCOUNT CREDIT is the same petty cash account. (Make sure you do not zerorise the currrnt year's figures.)

TIP

If your petty cash has many transactions, you will take some time to zerorise all amounts one by one. A faster way is to copy a zero figure and paste it to relevant cells using the Paste icon. Alternatively, paste to relevant cells using the Ctrl+V key combination.

10. Save the file.

11. Remove the filtering mode (menu Data/Filter/Show A11). Save again.

Repeat the operation for each petty cash account.

NOTE

Be careful not to replace the petty cash opening balances with 0 (zero). If this happen, just replace the figure.



166