New accounting year item 4 5 pg 151 152 153

5. Zerorise all Profit & Loss amounts in cells under column title RM-DEBIT relating to the previous year's transactions

We will replace all profit & loss amounts for the transactions for the previous year with 0 (zero) and update the amount for the UNAPPROPRIATED PROFIT B/F account.

Here is how you do it quickly:

In cell AB5 (column TEMP.), write this formula (overwrite existing content, if any):
=IF(AND(VLOOKUP(G5, INDIRECT("TB!A4:B2000"),2,FALSE)="UNAPPROPRIATED PROFIT", B5≤#12/31/2002#),0,H5)

This formula will write 0 (zero) to the cell AB5 (a temporary column) if cell G5 (in sheet DATA) contains a Profit & Loss item (by relying on the chart of accounts in sheet TB) if the date in cell B5 is earlier than or equal to the previous year and (#12/31/2002# in this case). This formula will write #NA if it finds that the account is not in the list in the chart of accounts.

Next, copy this formula down to the last row in the same column (TEMP) in the data area (To copy down fast: Select the cell AB5. Double-click on its bottom-right handle. This will copy its contents down to the last cell). Review for #NA items and make corrections as necessary.

151

Then, highlight all cells in this same column (from AB5 down to the last cell). To highlight all the cells quickly: Select cell AB5. Hold down the Ctrl key and press the Shift key. While still holding down Ctrl and Shift, press the downward arrow key. All relevant cells will be highlighted.

Now, we will copy the value (not the formula) of each highlighted cell into a corresponding area in column ACCOUNT-DEBIT. To do this, click menu Edit/Copy (make sure the cells are still higlighted). Go to cell H5 (under column title RM-DEBIT). Click menu Edit/Paste Special. A dialog titled Paste Special will appear. Under the Paste label, select Values (This means we want to copy the value, not the formula). Click OK. You should find that the Profit & Loss amounts under column RM-DEBIT now have been replaced with 0 values (without the formula).

6. Zerorise all Profit & Loss amounts in cells under column title RM-CREDIT relating to the previous year's transactions

Replace all amounts in column RM-CREDIT with 0 (zero) if its corresponding item in column ACCOUNT-CREDIT is a profit & loss item from the previous year.

Here is how you do it quickly:

In cell AB5 (column TEMP.), write this formula (overwrite existing content, if any):
=IF(AND(VLOOKUP (15, INDIRECT("TB!A4:B2000"), 2, FALSE)="UNAPPROPRIATED PROFIT", B5≤#12/31/2002#,0,H5)

This formula will write 0 (zero) into cell AB5 (a temporary column) if cell 15 contain a profit & loss account, and if cell B5 contains a date earlier than or equal to the prerious accounting year end (#12/31/2003# in this care). Otherwise it will write whatever amount is in cell J5 (under column RM-CREDIT).

Next, copy this formula down to the last row in the same column (TEMP) in the data area (To copy down fast: Select the cell AB5. Double-click on its bottom-right handle. This will copy its contents down to the last cell).

152

Then, highlight all cells in this same column starting from cell AB5. To highlight all the cells quickly: Select the topmost cell containing the formula (AB5). Hold down the Ctrl key and press the Shift key. While still holding down Ctrl and Shift, press the downward arrow key. All relevant cells will be highlighted.

Now, we will copy the amount (not the formula) of each highlighted cell into a corresponding area in column RM-CREDIT. To do this, click menu Edit/Copy. Go to cell J5. Click menu Edit/Paste Special. A dialog titled Paste Special will appear. Under the Paste label, select Values (This means we want to copy the value, not the formula). Click OK.

You should find that each amount under column RM-CREDIT where its corresponding item in column ACCOUNT-CREDIT is a Profit & Loss item has now been replaced with the figure 0 (without the underlying formula).

Save the file.