Printer Friendly

Spreadsheets.

Many small libraries have a checking account on which checks are written and to which deposits are made. Even if the checkbook described below is not needed in your library, this Lotus application may be useful to you personally.

The Worksheet: Dates

Several columns will be used for the information to be included in the Lotus checkbook. The first column will be left for the user to fill in the date. Since dates require the use of an @ DATE() function, and most users prefer not using this function, date information will be entered as a response to a {GETLABEL} command, and entered as a label.

Since the next column will transform this label into a Lotus date, the first column can be narrowed, using , worksheet, column set. Also, this column can be formatted for hidden, using , range, format, hidden. By hiding this column, you will prevent the appearance of two dates for every entry on the worksheet.

The second column will use an @DATEVALUE() function to transform the entered label into a Lotus date. For example, in cell B4 the function will be:

@DATEVALUE(a4)

The function can be copied down the worksheet using the copy command. Because of the relative address, the function will remain correct. The cells in this column will be formatted for date, using , range, format, date, and an appropriate date format will be chosen.

Unfortunately, this function will display text whether or not a data has been entered in the column to the left. To avoid this problem, an @IF() function can be written that incorporates the @DATEVALUE() function and an @CELLO function.

@IF(@CELL("TYPE",A4..A4)--"B",

""@DATEVALUE(A4))

This function win display an empty cell if the adjacent cell is blank.

Transaction Type The next column will be used to record whether a check has been written, a deposit made, or a withdrawal made. It will be filled in by the macro with either a C, D, or W. Formulas in another column will perform certain calculations based on whether there is a D in the cell in this column.

Check Numbers

The fourth column will be used for check numbers. To fill in the check numbers, place the cell pointer in the first cell that will be used in this column for check numbers. Enter the first check number 100, for instance.

In the cell under this, enter the formula D4+1, assuming that the first cell in this column that will have a check number is cell D4. Copy this formula down the column using the copy command. The check numbers will be displayed.

When deposits and withdrawals are made, the check numbers will not be needed; if a row is inserted or the numbers moved, the underlying formulas will not be correct. To avoid this problem, use , range, value to transform the formulas into the displayed values.

Text Columns

The next two columns will be used to record text which will be supplied as responses to {GETLABEL} commands. One column will be for the person or institution to whom the check was written while the other will be for the purpose of the check.

Amounts

The next column will be used for the amount of the check, deposit, or withdrawal. This amount will have to be entered as a number so a {GETNUMBER} command will be used to solicit the response rather than a {GETLABEL} command. This column should be formatted for currency with two decimal places. To format for currency, use , range, format, currency, 2.

Balance

The final column will compute the balance in the account. The formulas in this column will have to do several things:

* If there is no amount in the amount column, this column will not display anything.

* If there is a D in the column recording the type of transaction, this column will have to add the amount to the previous balance.

* If there is a C or a W in the transaction column, this formula will have to subtract the amount from the previous balance.

These will be performed by the following function:

@IF(@CELL("TYPE",C4..C4)="B","",

@IF(C4="D",@SUM

(G4+H3),@SUM(H3-G4)))

This function assumes that the transaction type is in column C, the amount is in column G, and the balance is in column H. This function can be copied down the column using the copy command. This column should be formatted for currency with two decimal places. To format for currency, use /, range, format, currency, 2.

The Macro

The macro, which can be named "B" so that it will execute whenever the worksheet is opened and Ne" so that it can be branched to within the macro, begins by moving the cell pointer to the portion of the worksheet that contains the checking account records. In the sample, this begins at cell A2.

{GOTO}a2|

The macro then displays a user-designed menu in the control panel. The menu is displayed using a {MENUBRANCH) command. In the sample, the (MENUBRANCH) is in cell K2. Following the (MENUBRANCH) command on the same line, there is a (BRANCH k2) command, which, if the Escape key is pressed while the menu is being displayed, will branch back to the same user-designed menu.

The user-designed menu has three options: select, which is the default and merely used to furnish instructions that are written in the line of the worksheet directly under the choice and can only be seen when the cell pointer is highlighting this choice; use, which is to use the macro; and quit, which is to quit the macro.

Each menu option will use a {BRANCH} statement that will tell Lotus to continue macro operation at a named range on the worksheet. If the default is selected in error, the macro will branch back to this user-designed menu. If the use option is chosen, the macro will use the following statement to branch to the macro named "\m."

{BRANCH\m}

Use

If the user selects the use option, the cell pointer is moved to the next available cell in the first column using the following command.

{END}{DOWN}DOWN}

A prompt is issued to have the user supply the date in the form of month/day/ year. The prompt is issued using a

{GETLABEL} command.

{GETLABEL "Enter date as mo/da/yr: ", ans}|

The user's response is placed in a range named "ans." The response is then copied to the currently active cell using the copy command.

'/cans||

Instead of using the {GETLABEL} command and range named ans to store the user's response, the /Xl command could be used. The '/xl command permits the user's response to be placed in the currently active cell.

In the column next to the date entered as a label as a response to the {GETLABEL} command, the @DATEVALUE() function has been written. Therefore, the macro will move the cell pointer to the right two columns.

{RIGHT 2}

At this point, another user-designed menu is displayed in the control panel. This menu will determine if a check has been written, a deposit made, or a withdrawal made. Each option will cause the macro to branch to a different range named on the worksheet. For example, if the user chooses the option check, the macro will branch to a range named "check" for further macro instructions. BRANCH CHECK)

In the sample, there are actually four menu options. The first choice, which can be considered as the default, has directions. If this option is chosen, the macro will branch back to this user-designed menu. In addition, after the {MENUBRANCH} command that displays this user-designed menu, there is a (BRANCH) command that will branch back to this user-designed menu if the Escape key is pressed while the menu is being displayed.

Check Option

If the user chooses the check option, the transaction column, which is currently active, will have to have a C placed in it. To accomplish this, the macro can use a {LET} command.

{LET ans, C}|

The C is then copied to the currently active cell using the copy command.

/cans--

Instead of using the LET) and copy commands, the macro can place a C in the currently active cell by having the C in the appropriate spot in the macro. Once the macro moves the cell pointer, the C will be recorded in the then active cell. The figures illustrate both techniques.

{GETLABEL} and {GETNUMBER} commands are then used to prompt for more information and the cell pointer is moved to the appropriate columns. After all the information has been filled in, the macro pauses using a {?} command, which will pause macro operation until the Enter key has been pressed. The pause can be used to verify the information and make any modifications that may be necessary. Once the enter key has been depressed, the macro branches back to the original user designed menu, which in the sample was named both \0 and \c.

Two techniques that may be used for this portion of the macro can be seen in Figures 1 and 2.

Deposit Option

If the user chooses the deposit option, the macro has to place a D in the transaction column, which is currently active. This can be accomplished just as the C was placed above.

Because this is a deposit, the check number is not relevant. The macro will now move the check numbers down one row using the move command after moving the cell pointer to the appropriate column.

{RIGHT}

'/m{END} {DOWN}|{DOWN}|

Now {GETLABEL} and {GETNUMBER} commands are used to solicit information. After the information has been entered, the macro pauses using a (?) command, which will pause the macro until the Enter key has been pressed. Once the Enter key has been pressed, the macro will branch back to the original user-designed menu that has been named "macro \0" and '\c."

Two techniques that may be used for this portion of the macro can be seen in Figures 3 and 4.

Withdrawal Option

The withdrawal option operates in a manner similar to the two previous options: a W is placed in the transaction column. Then the check numbers are moved because a withdrawal does not require a check number. Prompts are issued using {GETLABEL} and {GETNUMBER} commands. When all the information has been entered, the macro pauses using a {?} command that will pause the macro until the Enter key has been pressed. Then the macro branches back to the original user-designed menu, named macro \0" and "\c."

Figures 5 and 6 show two techniques that may be used for this portion

Quit Option

The original user-designed menu has the option to not use the macro. This option can lead to one command: (QUIT). This option allows the user to use the checkbook without using the macro.

Saving the Worksheet

The macro described above can be modified to include the file save option. The command can either be a separate option in the original user-designed menu, or it can be incorporated into one of the menu choices.

If you want to incorporate it into the quit option, add a line as follows before the QUIT) command.

'/fs|r

This assumes that the quit option will be chosen directly before the worksheet is exited. If this is the case, instead of the QUIT) command, the macro can have the following command:

'/qy

This command will end the Lotus session.

Instead of this, each of the menu options in the second user-designed menu can include a few lines instead of the final (BRANCH) command. These commands will save the worksheet by replacing the worksheet on disk with the current one, and then retrieving the just-saved worksheet.

'/fs|r

'/frcheck|

This assumes that the worksheet has been named "check." If the original user-designed menu was named both '\0" and '\c," the opening menu will be displayed when the worksheet it again retrieved.

Reconciling the Account

The only change that needs to be made to reconcile the account is to add two columns to the worksheet. One column will place a mark in the cell showing that the transaction recorded in the checking account is also on the statement. The other column will contain a formula that will keep a balance related to those transactions on the bank's statement.

Statement Column

The first column can be used to record any mark to signify that the transaction has been recorded on the bank statement. This can be a letter of the alphabet, a number, or a date. The mark does not matter because the function in the next column will look to see if the cell is blank and do one thing if it is, another if it is not.

Bank Balance Column

The bank balance column relies on a complicated @IF() function. In cell J4, the following function can be written:

@IF(@CELL("TYPE",i4..i4)="B",+j3,

@IF(c4="D",+j3+g4,i3-g4))

This @IF() function states that if the cell in this row which records whether the transaction is on the bank statement is blank, copy the amount from the above cell in the bank balance column to this cell. If the statement column is not blank, use another @IF() function to test the contents of the column which records the type of transaction. If this column contains a D, add the bank balance from the above cell to the amount in the amounts column. Otherwise, which means that either a C (for check) or W (for withdrawal) has been transacted, subtract the amount from the bank balance in the cell above.

Instead of using +j3+g4 and +j3-g4, @SUM() functions can be used. The results will be identical.

This function can be copied down the column using the copy command. Because of its relative cell addresses, the copy command will properly adjust all references.

Note

As the function has been written, it will record the balance in every cell into which it is copied. The function can be modified to display a value only when there is something in the corresponding amount column. The modified function, which also can be copied using the copy command, is as follows.

@IF(@CELL("TYPE",G4..G4)="B

@IF(@CELL("TWE",i4..i4) ="B",+j3,

@IF(c4="D",+j3+g4,i3-g4)))

With this function, the bank balance will only be seen until the last used line in the checkbook record.

Interest-Bearing Accounts

If your checking account has interest added to it, the worksheet and macro can be modified. The functions in the balance and bank balance columns will have to be modified to permit the worksheet to add the amount if an I (for interest) appears in the transaction type column. The modified balance function, which can be copied down the column using the copy command, is as follows.

@IF(@CELL("TYPE",C4..C4)="B","",

@IF(C4="D"#OR#C4="I",

@SUM(G4+H3),@SUM(G4-H3)))

This function will add amounts in the amount column with either a D or I in the transaction PM column. If a C or W appears in the transaction type column, the amount will be subtracted.If nothing appears in the transaction type column an empty cell will be displayed.

Bank Balance Column Modification

The bank balance column's function may be modified in a similar manner. The modified function is as follows.

@IF(@CELL("TYPE",G4..G4)="B","",

@IF(@CELLX"TYPE",I4..I4)="B",+J3,

@IF(C4="D"#OR#J4="I",+J3+G4,J3-G4)))

This function will add deposits and interest, and subtract checks and withdrawals from the above bank balance. If no amount appears on a corresponding line, this function will display an empty cell. This function can be copied down the column using the copy command.

Macro Modification

In the user-designed menu, another option may be added to permit the user to enter the interest into the checking account record. This option proceeds as the deposit option did, placing an I in the transaction type column, moving check numbers, and then prompting the user for various pieces of information and moving the cell pointer. The option ends with a pause furnished by a {?} command. Once the Enter key has been pressed, the macro branches back to the original user-designed menu.

Two techniques that may be used for this portion of the macro can be seen in Figures 7 and 8.

Macro Power

This application demonstrates some of the power of macros. Whether they are used to simplify your checking account or directed toward another problem, learning to write macros is well worth the effort.
COPYRIGHT 1991 Information Today, Inc.
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 1991 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:a Lotus 1-2-3 tutorial
Author:Machalow, Robert
Publication:Computers in Libraries
Date:Oct 1, 1991
Words:2761
Previous Article:Lotus 1-2-3 for DOS Release 2.3: refining WYSIWYG.
Next Article:Local area networks.
Topics:

Terms of use | Privacy policy | Copyright © 2019 Farlex, Inc. | Feedback | For webmasters