Method 3: Use Formula in Column TEMP
In this method, we will use a formula in column TEMP to extract amounts from column RM-DEBIT and zerorise the amount if its corresponding item in column ACCOUNT-DEBIT is a petty cash account, and the date in column DATE is earlier or equal to the previous year ending date, then paste back the figure as value (without formula) into the RM-DEBIT column. Then, we will repeat (extract) for amounts from column RM-CREDIT and zerorise the amount if its corresponding item in column ACCOUNT-CREDIT is a petty cash account, and the date in column DATE is earlier or equal to the previous year ending date, then paste back the figure as value (without formula) into the RM-CREDIT column.
1. Go to cell AB5 (under column title TEMP).
2. Write this formula in the cell (overwrite its contents):
=IF(AND(G5="PETTY CASH A", B5=<#12/31/2003#),0,H5).
This formula will put zero into cell AB5 if the account name in cell G5 is PETTY CASH A and the date is earlier than or equal to 12/31/2003, otherwise the original amount will remain. (Replace PETTY CASH A with your actual petty cash account name in the formula and #12/31/2003# with your actual previous year end date).
3. Copy this formula down the column until the last row of the data area. (To copy down fast, double-click the handle at the bottom-right of the cell AB5). This will put zero into all cells in column AB (starting from row 5) if the corresponding accounts name in column ACCOUNT-DEBIT is PETTY CASH A, otherwise the original amount will remain.
4. Next, we want to copy the values in column AB into the column RM-DEBIT. To do this, highlight all the cells in column AB (from row 5 to the last row of the data area). (To highlight fast, hold down the Ctrl key and press Shift. While still holding down both keys, press the downward arrow key.) Then click menu Edit/Copy. Go to cell H5. Click menu Edit/Paste Special to open the Paste Special dialog box. Select Values. (This means we want to paste values, not formulas). Click OK. The new amounts should have replaced the figures in column RM-DEBIT.
167
5. Next, we want to do the same on the column RM-CREDIT. The process is similar; only change some parameters in the formula.
6. Go to cell AB5.
7. Write this formula in the cell (overwrite its contents):
=IF(AND(I5="PETTY CASH A", B5=<#12/31/2003#),0,J5)
This formula will put zero into cell AB5 if accounts name in cell 15 is PETTY CASH A, and the date is earlier then or equal to 12/31/2003 otherwise the original amount will remain. (Replace PETTY CASH A with your actual petty cash account name in the formula and #12/13/2003# with your actual previous year end date).
8. Copy this formula down the column until the last row of the data area. (To copy down fast, double click the handle at the bottom-right of the cell AB5). This will put zero into the cells in column AB (starting from row 5) if the corresponding accounts name in column ACCOUNT-CREDIT is "PETTY CASH A", otherwise the original amount will remain.
9. Next, we want to copy the values in column AB into the column RM-CREDIT. To do this, highlight all the cells in column AB (from row 5 to the last row of the data area). (To highlight fast, hold down the Ctrl key and press Shift. While still holding down both keys, press the downward arrow key.) Then click menu Edit/Copy. Go to cell J5. Click menu Edit/Paste Special to open the Paste Special dialog box. Select Values. (This means we want to paste values, not formulas). Click OK. The new amounts should have replaced the figures in column RM-CREDIT.
10. Check that the absolute total of RM-DEBIT (in cell H1) and RM-CREDIT (in cell J1) is still the same. Otherwise, you may not have identified the amounts correctly. A review of the bank accounts may help you identify the reason.
Repeat the steps for each petty cash account (if you have more than one).
NOTE
Be careful not to replace the petty cash opening balances with 0 (zero). If this happen, just put back the figure.
168
No comments:
Post a Comment