Check Book Register on Paper and In 1-2-3 : Graphic
Like a paper checkbook register, you enter your financial data in the cells created by the intersection of the rows and columns. Unlike a paper check checkbook register, you also can create and copy formulas to cells, which tell 1-2-3 to automatically add, subtract, multiply, divide, or carry out other math functions on the data you've entered, and display the results. In the case of the above example, you've created a self-balancing checkbook.
What can you do with a spreadsheet?
Spreadsheet programs let you manipulate text, numbers and calendar (date and time) data. Data and formulas are entered in Cells, as mentioned above. In fact, any collection of data that is organized in records and fields can be entered or brought into a 1-2-3 spreadsheet. This means that 1-2-3 can be used as a simple, flat-file database. For example, you could create a spreadsheet that contained a list of your co-workers names, phone numbers, and E-mail addresses.
The 1-2-3 Release 2.x worksheet consists of a two-dimensional grid of cells 256 columns wide and 8,192 rows deep. This is a BIG spreadsheet much larger than any check register or ledger book an accountant would typically use. The spreadsheet size you use however is constrained by available system RAM memory on the Palmtop. Memory is further constrained by the number of applications you have open at the same time. With no other Palmtop applications open, you can have a spreadsheet containing about 290 kilobytes of data. With five applications open (Appt, Phone, Database, Memo and 1-2-3) there will be approximately 45 kilobytes available for your spreadsheet data.
Starting and leaving 1-2-3
Start 1-2-3 on the HP Palmtop by pressing (1-2-3). To exit and close the 1-2-3 program press (MENU) Quit Yes. If you have not yet saved the worksheet you are working on, 1-2-3 prompts you, WORKSHEET CHANGES NOT SAVED! End 1-2-3 anyway? If you press Yes, 1-2-3 quits and you loose your work. If you press No, Lotus goes back to the worksheet you are working on. To save a 1-2-3 worksheet, press (MENU) File Save, give the worksheet a name, and press (ENTER).
One of the nice things about the HP Palmtop is that you don't have to quit 1-2-3 to go to another built-in application. If you're working on a spreadsheet and need to consult your Appointment Book press (APPT). When you're finished with APPT, press (123) and you're back where you left off.
Understanding the 1-2-3 screen
One of the first basic skills necessary is understanding how to read the 1-2-3 screen.
The 1-2-3 screen consists of three primary areas: the Worksheet Area, the Control Panel, and the Status line (see screen at bottom of this page).
The Worksheet Area
The worksheet area occupies most of the screen. It is where you see data that you have entered in the spreadsheet. The worksheet area displays only a part of the 256 columns and 8,192 rows possible in each spreadsheet. In the above graphic, the worksheet area is columns A through F, and rows 1 through 20. (Note: the worksheet area can be displayed on the Palmtop in two screen sizes: 6 columns by 13 rows and 8 columns by 20 rows, assuming columns 9 characters wide. Press (Fn)- (Spacebar) to toggle between them. Screen shots in this article are displayed in 8 by 20 format.)
Three Areas in 123 Screen : Graphic
Caption: The 1-2-3 screen consists of three areas: The "Control Panel" -- the top three lines of the display; the "Worksheet Area" containing the rows and columns of the spreadsheet itself; and the "Status Line" at the bottom, displaying time, date, and other status information.
The worksheet area is bounded on the left by the Row Indicator bar and on the top by the Column Indicator bar. Each intersection of a column and row forms a cell. A cell is the basic storage unit of a worksheet, in which you enter data, formulas, or text. The location or Address of each cell is specified by a column label and a row label. The 256 columns are labeled alphabetically, beginning with A and going through Z, then AA through AZ, BA through BZ, and so on, all the way to IA through IV. Rows are labeled numerically (1 through 8192). For example, C5 identifies the cell at the intersection of column C and row 5.
The highlight bar or cursor in the worksheet area, is called the Cell Pointer. It identifies the current cell (i.e., the cell into which you can enter data or formulas). You can move the cell pointer to different cells with the arrow keys and other pointer movement keys. (Well describe these other pointer movement keys in another issue when we get into some more advanced cell editing and data entry concepts). For now, use the arrow keys to move the cell pointer to cell C5. The cell itself is highlighted, the column bar above is reversed on the C, and the row bar is reversed at the number 5. (This did not display well on the screen graphic we showed earlier, but does on the Palmtop.)
In this example, C5 is the current cell that your next entry or procedure will affect. Type 1234 and then hit (ENTER) to place the numbers 1234 in cell C5.
The Control Panel
The control panel is the three-line area at the top of the screen. The first line displays information about the format of the current cell and the data in it. The second and third lines list command menus that let you choose the actions you want 1-2-3 to perform. (Well look at the command options in a future issue.)
Line 1: Cell information The first line of the control panel displays the cells address, its format, protection status, column width, and the contents of the cell. (The cell format, protection status and column width are only displayed if default settings have been changed). At the end of the first line is the Mode Indicator. Well use the above example in which the number 1234 was entered into cell C5 to help clarify these items.
Although we selected 0 decimal points in the above examples, we could have formatted 1-2-3 to display a number with one or more decimal points. We can also enter negative currency amounts, which appear in brackets in the cell (e.g., key in -231 and ($231) will be displayed). Note that on the first line of the control panel at the top of the screen, the negative sign is displayed for the cell contents. Experiment with other range formats to get a feel for how this works.
Lets recap, using the sample first line shown at the end of the last paragraph. The first line of the control panel, from left to right, displays the following:
C5 -- the address of the highlighted cell.
(C0) -- any formatting you've done to the cell.
U -- whether you've protected the cell or not.
(W7) -- Cells column width, if default changed.
1234 -- the cell contents.
Check out the sidebar at the end of the article for more details on changing the column width.
Numbers -- numeric data, like deposits and withdrawals from a checking account.
Formulas -- equations that tell 1-2-3 what to do with the data you enter into the cells.
The status line is the line at the bottom of the screen displaying the date and time indicator. You can change Lotus to display the filename of the worksheet currently loaded instead of the date and time. To do so, press (MENU) Worksheet Global Default Other Clock Filename. Additional status indicators may be displayed on different parts of the status line, including CAPS (when the Caps Lock key is on), or CALC (when the worksheets formulas need to be recalculated).
Save your spreadsheets
Most of the Palmtops built-in applications automatically save data. This is not the case with 1-2-3. Save your worksheet by pressing (MENU) File Save. A prompt will then appear on the second line of the control panel stating: Enter name of file to save: C:\_DAT\*.wk1. Lotus is set up to save files to C:\_DAT on the Palmtop. To change this, press (MENU) File Directory and key in the complete path of the new directory. If you have not yet saved your file, key in the filename and press (ENTER). If you have already saved it, and are just re-saving it because you changed the worksheet, the filename should already be displayed. All you have to do is press (ENTER).
After you press (MENU) File Save, Lotus will let you save a file to another directory. Below the second line from the top, Lotus displays an abbreviated list of worksheets saved in the current directory along with a list of any existing subdirectories. (You can press (F3) at this point to get a full screen display of this list.) Use the ArrowKeys to move the highlight bar to the desired subdirectory and press (ENTER). 1-2-3 now lists the files and other subdirectories in the subdirectory you highlighted.
You can also save the current worksheet under the name of another already existing worksheet. Simply highlight the existing filename and press (ENTER). Lotus will ask you if you want to Cancel the save, Replace the file with the worksheet you're saving, or Backup the existing file before you replace it. Be careful! If you choose Replace, you loose that other worksheet.
Retrieving your work
If you've saved your work and exited 1-2-3, you'll later want to retrieve it. To do this, start Lotus and press (MENU) File Retrieve and type in the complete path and name of your worksheet file. (E.G., for the worksheet BUDGET.WK1 in the CASH directory of your memory card you would type in a:\cash\budget.wk1.)
If you don't remember the path and/or name of your worksheet, you have to go find it. Note that when you pressed (MENU) File Retrieve, Lotus displayed a list of existing worksheets below the second line, with the cursor resting on the first of these files. Use the ArrowKeys to move through the files and subdirectories until the desired filename is highlighted. Then press (ENTER) to load it.
Limits to the size of your lotus spreadsheets
Theoretically, you can create a spreadsheet 256 columns wide by 8192 rows long. However, the actual size of a spreadsheet is limited by the amount of system RAM you have available. This means that if you try to retrieve a spreadsheet that is too large, 1-2-3 will partially load its data and then beep at you. The mode indicator will say ERROR and a dialog box will appear indicating Memory Full.
Remember the F1 key? It accesses Lotus context sensitive Help screen. Press (F1) at this point and Lotus will give you some suggestions on how to deal with too little memory. The primary way is to close down other built-in applications to make more memory available.
Strictly avoid one of the Help suggestions DO NOT save the recently loaded file. Lotus only partially loaded it. If you save it, you'll write over the original, loosing some of its data. Instead, hit (ESC), and type (MENU) Worksheet Erase Yes Then free up memory by closing other applications. Once enough memory has been freed, you should be able to load that larger file.
To tell how much memory is available for a Lotus worksheet, Press (MENU) Worksheet Stat. The first line of the Worksheet Status box displays the amount of conventional memory available for a worksheet. Remember that number. Then press (ESC) to go back to the main screen and (MENU) File Retrieve to load a worksheet. When you highlight an existing worksheet, its file size is displayed on the third line, all the way to the right of the display.
Congratulations!
If you've gotten this far, and followed what's been said, you are now a qualified 1-2-3 user. You understand what a cell is, and how to get data (both numbers and labels) into a cell. You know how to format the presentation of numeric data and how to move around in the worksheet using the arrow keys. You know how to save and retrieve your work. You've used the 1-2-3 command menu and can begin exploring that on your own. You understand how to read the 1-2-3 screen. You've even used two or three function keys. That's a lot for the first time around.
Future articles will focus on refinements of some of these basics. Next
month well discuss formulas and 1-2-3s @functions. In the meantime get
some data into 1-2-3 and experiment. Try replacing your check register
with a Lotus spreadsheet, set up a spreadsheet for your personal or business
budget, document your workout plan in Lotus. Play around with the concepts
we've discussed and have fun!