The Spreadsheet Manual
Part Two, Reference
The Spreadsheet menu
The Grid menu
The File menu
The OPL Function
The Spreadsheet menu
When the grid is being displayed, press the MODE key to view the Spreadsheet command menu. This menu contains the following items:
|Grid||Enter the Grid command menu.|
|File||Enter the File command menu.|
|Goto||Go to another cell in the grid.|
|Edit||Edit the contents of the current cell.|
|Info||Switch between hiding/displaying formulae.|
|Titles||Switch between hiding/displaying titles.|
|Quit||Leave the Spreadsheet and free the memory used by it.|
Two Items in this menu, Grid and File, each lead to a sub-menu of commands when selected. These are detailed further below. The remainder of the commands In the Spreadsheet menu have the following uses:
Puts the display Into insert mode. The contents of the current cell - or, if a formula has been entered, the formula which controls the current cell - can then be altered on the bottom line of the display.
Edit can only be used to edit one cell at a time - the cell that was current at the time the command was selected.
Moves you to another cell in the grid. When selected, the bottom line of the display shows the prompt:
The default cell when titles are displayed is B2; otherwise the default is A1. If there is a circular reference in the current worksheet the default offered is one of the cells which contribute to the circular reference.
Info and Titles
Control the Spreadsheet's four display modes (see reference chapter display modes).
The Titles command switches between Titles on and Titles off.
The Info command switches between Info on and Info off.
Exits the Pocket Spreadsheet and frees all memory used by it. If the current worksheet has been modified since it was last saved, the Spreadsheet requests confirmation before EXEcution of the command.
When executed, the effects of this command cannot be reversed. So ensure that the current worksheet can safely be discarded before selecting Quit.
To return to the Organiser top level menu without deleting the whole of the current worksheet, just press ON/CLEAR while the grid is displayed. The current worksheet and all data will remain in memory and you can return to it at a later date. When you return to the Spreadsheet with a worksheet in memory, you are returned to the last cell you visited.
The Grid menu
When the Grid command in the Spreadsheet menu is selected a further menu is displayed, containing the following Items:
|Copy||Copies the contents of one range of cells to another range.|
|Delete||Deletes the contents of a range of cells.|
|Format||Sets the numeric display format for a range of cells.|
|Prints out the values of a range of cells.|
|Recalc||Sets the formula recalculation mode.|
|Widths||Sets the column width for a range of columns.|
|Zap||Clears all data from the grid.|
These commands are concerned with processes which can be effected on the grid itself or on the contents of the grid. The menu items Delete, Format, Widths, Print and Copy all prompt in the same way for one or more ranges of cells on which to operate:
Defining a cell range
First the bottom line of the display shows the name of the command selected, followed by two copies of the current cell reference. For example, if the Delete command is selected when the current cell is H 16, the bottom line of the display shows:
with the cursor flashing over the first 'H'. The two cell references represent the top left cell and the bottom right cell of a rectangular area of the grid. This is the range of cells which will be affected by the command. To change the range either:
Pressing ON/CLEAR while you are editing the range clears your edit and returns it to what it was when the command was first selected - in this example:
You can now begin altering the cell range again, as described above, or press ON/CLEAR to abandon the command and return to the grid.
The commands in the Grid command menu have the following uses:
Deletes a range of cells from the grid. When Delete is selected the Spreadsheet prompts for a range of cells.
When the cells in the range indicated have had their contents deleted, there is no way to retrieve them. Use this command with care.
Sets the numeric display format for either a specified range of cells or for all blank cells in the grid.
When Format is selected, a two item menu is displayed, containing the items Default and Cells.
Once the cell range has been entered if you select Cells, or immediately if you select Default, this four-item menu is displayed:
When Fixed or Scientific are selected, the bottom line of the display prompts for the number of decimal places. In both cases, the default 2 is offered and the maximum allowed is 6.
When Integer or General are selected, the display returns immediately to the grid with that numeric display format in effect.
Sets the column width for a range of columns. Only whole columns may be affected; you cannot set, for example, cells 1, 2 and 3 in row F to be 5 characters wide and the rest of column F to be 7 characters wide
Once you have selected the cell range that the width command is to effect, the bottom line of the display prompts for the new column width.
Any width from 1 to 12 may be entered (6 is the maximum for column A). The default cell width is 7 characters on model LZ arid 5 characters on model X
Prints the current worksheet to a printer, one page column at a time; i.e. a worksheet would be printed page by page in this order:
When Print is selected, if there is no data in the grid (i.e. you have just started the Spreadsheet or used the Zap command) you are returned immediately to the grid. Otherwise, a two-item menu is presented:
After selecting either of these, you are prompted for a page width. The default width is 80 characters.
The selected range of cells (or the whole worksheet) is then printed, with a form feed character printed at the end of each page of cells.
The Print command will operate only when a Comms Link cable or Psion Printer II is connected to the Organiser. If neither of these is connected, a DEVICE MISSING error will be displayed. You will then be returned to the grid.
Copies a range of cells to another position in the grid. When Copy is selected, the command prompts for the source cell range and then the destination cell range.
Both display values and formulae are copied to the destination range.
Formulae containing absolute cell references are copied with the same absolute cell references intact. Formulae containing relative cell references are copied with a new relative cell reference modified from the original (see Relative and absolute cell references).
Wherever two identical formulae appear in the worksheet, only one formula is stored, and all cells referring to it share the one copy. The Organiser always makes the most efficient use of memory that it can.
If the size of the destination range is greater than the source range, the source range is replicated within the target range for as many complete copies as will fit in that range.
If the source range and destination range overlap, the data will still be copied across cell by cell. This means that some data may be overwritten accidentally.
For example, in the worksheet below there would be a one-cell overlap if the source range was B2:C4 and the destination range was C4:D6
| |B |C |D |2 | 1| 2| |3 | 3| 4| |4 | 5| 6|
The Copy command works across the first row, then the next and so on.
So in this example, B2 is copied to C4. However, this is in the source range too, and the value which was there has now been deleted. The next four cells, C2, B3, C3 and B4 are copied without mishap. The final cell, which previously held the value six, has been given the value 1, and this is what gets copied to cell D6.
So the original range of cells with the values:
has now become:
with the bottom right hand cell corrupted.
Sets the recalculation mode. The two-item menu Manual and Automatic is presented.
Clears all data from the worksheet and allows you to begin entering new data. If the current worksheet has been modified since it was last saved, you will be prompted to confirm that you want to delete all data.
Ensure that all important data is saved to a device before using this command, as it cannot be reversed.
The File menu
All the commands in the File menu are concerned wit the transfer of worksheets to/from:
The File menu contains the following items:
|Load||Load a worksheet file.|
|Save||Save a worksheet file.|
|Import||Import a worksheet file from a PC or Macintosh.|
|Export||Export a worksheet to a PC or Macintosh.|
|Dir||Read the directory of worksheet files.|
|Erase||Erase a worksheet file.|
Loading a Spreadsheet file
Load replaces any currently displayed worksheet with the new worksheet. So if the current worksheet has been modified since it was last exported or saved. you are prompted to confirm that you want to load in a new file. Press N to abort, or Y to continue the Load.
The file name must be up to 8 alphanumeric characters, starting with a letter. (On model LZ you can press EXE as usual to get a directory list.)
On completion, you are returned to the grid with the loaded worksheet displayed.
Saving a spreadsheet file; passwords
The file name must be up to 8 alphanumeric characters, starting with a letter. If the file name already exists on the current device, you are asked to confirm whether or not to delete the old worksheet. Once you have entered the file name, you are prompted to add a password.
Remember that if you do add a password you will have to enter it each time you wish to load this worksheet.
When you've entered the file name and/or password, if you get an OUT OF MEMORY message, change to another device by pressing MODE - or abandon the Save.
If you get a BATTERY LOW message, press the SPACE key and then ON/CLEAR to leave the Spreadsheet, with your worksheet still intact in memory. Change the battery as explained in your Organiser manual. Then return to the Spreadsheet and Save the worksheet as planned.
On completion of the Save, you are returned to the grid. It is best to save worksheets on a Rampak, or a PC or Macintosh, since:
The saved worksheet remains current until you use Zap, Quit, Load or Import. It is recommended that you Save worksheets and then Quit, rather than just ON/CLEAR out of the Spreadsheet leaving the current worksheet taking up internal memory.
Dir (directory of Spreadsheet files)
Reads the directory of saved worksheets on the internal memory of the Organiser or on a Datapak or Rampak. When Dir is selected the display shows:
Erasing spreadsheet files
Select Erase and the display shows:
The file is deleted and you are returned to the grid.
Importing and Exporting spreadsheets
When you select either Import or Export:
Otherwise an error is reported and you are returned to the File menu.
When you select Import or Export, you are presented with a menu of spreadsheet file types. This is the type either:
The following file types are available:
In WKS, WK1 and WR1 files, formulae are transferred intact, provided that:
(Note for Apple Macintosh users: when exporting to or importing from a Macintosh, an extra file type is added to the menu: This is because spreadsheet packages on the Macintosh produce file types 'BINA' or 'TEXT'. WKS produces a 'BINA' file. If you find you can't load this file, try selecting type .WKT; this produces a file of the same format but of the type 'TEXT'. The same restrictions on formulae apply as with the .WK1, WR1 and WKS files.)
Include the pathname, or folder, if desired. Use the / character in place of the DOS backslash \. If you are connected to a Macintosh, use / to separate folder from file name.
E.g. to import the file SALES.WKS which is in the SPREAD directory or folder, select WKS from the Import menu and type:
You don't have to Include the file extension, provided that you select the correct file type from the menu.
If you donít include a file extension in the file name when Exporting, the default extension for the selected file type (.WKS or whatever) is automatically added.
When the Import is complete you are returned to the grid with the worksheet displayed.
NB. Imported worksheets must not cover a range greater than 26 columns wide and 99 rows long. Cells outside this range are discarded. Cell width settings are discarded during Import. Some columns may therefore need their widths altered after the file has been transferred.
When the Export is complete you are returned to the grid. Cell width settings are discarded during Export. so some columns may need their widths altered In the spreadsheet on your other computer after the file has been received.
ERRORS FOUND - not more than 15 errors have occurred.
TOO MANY ERRORS - more than 15 errors have occurred.
SERVER FILE ERROR - the other computer failed to open or write to a file.
LINK FAILED - the link between the computers failed, e.g. if the cable wasn't properly connected or the Comms Link program wasn't running.