Category : Lotus and other Spreadsheets
Archive   : LTSTIPS3.ZIP
Filename : LOTUSTIP

System Disk Trick
(PC Magazine Vol 3 No 17 Sept 4, 1984 User-to-User)

For users of Release 1A of 123 on hard disks who do not like to
get out their system disk each time they boot 123, from the DOS prompt
(or in an AUTOEXEC.BAT file) simply type 123 and hit Enter. This does
not work if you load the Lotus Access System before loading 123. When
the 123 copyright notice appears, place a blank unformatted disk in
drive A: and press any key. The blank 123 spreadsheet appears and
away you go.

-----------------------------------------------------------------
Transferring Worksheet Ranges
(PC World April 1984 The Help Screen)

Assume you have two worksheet files called ONE and TWO that are
too large to combine in a single file. Calculations in worksheet ONE
produce results in the range V102..V152. The method to automatically
enter the results in worksheet TWO (assume cells C22..C72) and
recalculate worksheet TWO is this.
First, load worksheet ONE and move the cell pointer to cell V102).
Next, use the command /Range Name Create, enter a name such as TRANSFER
LIST, and specify the range V102..V152. Then save worksheet ONE.
Now load worksheet TWO, move the cell pointer to C22, and give the
command /File Combine Copy NamedRange TRANSFER LIST. The entries in
cells V102..V152 of worksheet ONE will be copied into cells C22..C72 of
worksheet TWO. If formula recalculatino for worksheet TWO has been set
to manual (/Worksheet Global Recalculation Manual), press the F9 key to
recalculate worksheet TWO's formulas.
This procedure can be abbreviated to two keystrokes by using 123's
macros. Load worksheet ONE, move the cell pointer to an empty area,
and enter the following macro:

{goto}V102~
'/rncTRANSFER LIST~
V102..V152~
'/fsONE~
r
'/frTWO~

To name the macro, move the cell pointer to the cell that contains
the first macro command. Use the letter T for transfer. Select /Range
Name Create, type \T and press Enter twice. Save this worksheet and
its newly created macro. You can invoke this macro at any time by
pressing -T.
The remainder of the routine must be in worksheet TWO. For the
sake of convenience, refer to it by the same key combination as
the macro in worksheet ONE. Load worksheet TWO, move the cell pointer
to an empty area and enter the macro:

{goto}C22~
'fccnTRANSFER LIST~
ONE~
{calc}

Name this macro (/Range Name Create), type \T, press Enter twice,
and save worksheet TWO. Because the macros in both worksheets share
the same name, whenever worksheet ONE is loaded and 123 is in the Ready
mode, you can transfer the list of values from cells V102..V152 in
worksheet ONE to cells C22..C72 of worksheet TWO simply by pressing
-T twice.

CORRECTION (from PC World July 1985 The Help Screen):
If the range to be copied contains worksheet formulas, these
formulas -- rather than their current values -- are transferred to the
second worksheet. This does not solve the problem of transferring
values.
The answer, of course, is to use the /File Combine Add command
which adds the value of the cells from the first worksheet to whatever
is in the target cells in the second worksheet. It's also a good idea
to preface this command with a /Range Erase just to make sure that the
cells in the second worksheet are empty. To amend the procedure
described above, edit the macro for worksheet TWO to read:

{goto}C22~
'/reC22..C72~
'/fcanTRANSFER LIST~
ONE~
{calc}

-----------------------------------------------------------------
Leaving a Hole for a Variable
(PC Magazine Vol 4 No 13 June 25, 1985 Spreadsheet Clinic)

It's useful to be able to put variables into a macro. The trick
is to put a "hole" in the macro which is filled before the macro
executes.
Figure 1 involves two simple macros. You could use it as part of
a procedure to input monthly data according to a standard format and
save the data with the name of the month as the filename. The first
macro prompts for the name of the month and stores the name in cell C1.
After you have entered all the data for that month, you invoke the
second macro, which copies the name of the month into the "hole" in
cell A3. The macro then does a file save, using the name of the month
as the filename.
The key to using variables in macros is to understand that single
commands can be spread over several cells. The file save command that
starts in cell A2 doesn't end until cell A4. This is what lets you put
a variable in cell A3.
Figure 2 is a more complex example, which you might use to adjust
all the numbers in a column of figures by multiplying them by a
constant. The macro prompts for the constant and the address of the
column of figures and away it goes. The range name routine (cells AA6
through AA8) that stops the macro at the last number in the column is
from LeBlond and Cobb's book, Using 1-2-3.
Editor's Note: An example in User-to-User Vol 3 No 18 used this
technique but didn't elaborate on the principle. The macro in Figure 2
could be made even more flexible if there were a third prompt that
supplied the operation to be performed on the column of figures (in this
case the * in cell AA9). Any of the arithmetic operators would work.
There's nothing to stop you from slinging as many variables as you like
into your macros.
- - - - -
Figure 1: Simple Macros -- this procedure creates a slot that can be
filled with variable information

First Macro

/xlWhat is the month of this budget? : ~C1~

Second Macro

Cell A1 /cc1~A3~
Cell A2 /fs
Cell A3
Cell A4
- - - - -
Figure 2: Complex Macro -- This macro carries out an arithmetic
function with the constant and cell addresses supplied by the user

Cell AA1 /xlWhat is the adjustment? : ~AA10~
Cell AA2 /xlWhat is the column? : ~AA4~
Cell AA3 {goto}
Cell AA4
Cell AA5 ~
Cell AA6 /RNCTEMP~~
Cell AA7 /XITEMP=0~/RNDTEMP~/XGAA15~
Cell AA8 /RNDTEMP~~
Cell AA9 {edit}*
Cell AA10
Cell AA11 ~
Cell AA12 {edit}{calc}~
Cell AA13 {down}
Cell AA14 /xgAA6~

-----------------------------------------------------------------
Don't Justify Named Ranges
(PC Magazine Vol 4 No 14 July 9, 1985 Spreadsheet Clinic)

Don't use range names when you justify labels. The command
"/Range Justify named-range " will wipe out your range name.
Editor's Note: It won't really wipe out your range name; 123
remembers it. If you try a /Range Name Delete command, 123 will list
the range name on the command line. However, the range is no longer
valid, and when you try to do something with it (copy, move, etc.),
123 will tell you that it's an illegal range. This is a case of a
perfectly legal range being made an outlaw by having its contents
rearranged. The same thing will happen if you use the normal cell
address technique to justivy labels that happen to contain a named
range. 123 will still remember the range but will tell you it's
illegal any time you try to do something with it. Justification and
named ranges don't mix.

-----------------------------------------------------------------
Linked 123 Worksheets
(PC World July 1985 Star-Dot-Star)

It's not uncommon to have a series of 123 worksheets that must be
subjected to an identical group of procedures. This typically involves
loading a template, "combining-in" a file, performing various
operations, and printing the result. In the past we used a brute-force
approach. Today all our files contain a small macro named \0 at cell
B1 (any cell will do). The macro consists of {GoTo}B2~/FCCEzero~, and
cell B2 is blank. An empty worksheet named Zero is kept in each
subdirectory.
The effect of the macro is to combine the worksheet Zero below the
autoexecuting macro every time the worksheet is loaded. Usually Zero
is empty and the macro stops. To perform a series of operations on a
group of worksheets, just put the appropriate macro into cell A1 of
Zero to initiate the sequence for each worksheet. This allows a
worksheet to pass macro keystrokes to another file, a feature not
otherwise available in 123. In addition, the File Extract command can
put keystrokes into Zero that cause it to retrieve the next worksheet
in sequence.

-----------------------------------------------------------------
Combining Worksheets
(PC Magazine Vol 4 No 17 Aug 20, 1985 Spreadsheet Clinic)

It's often necessary to take selected data from a worksheet on
disk (call it DISK.WKS) and read it into the worksheet you are
currently working on (SCREEN.WKS). If, when you last saved DISK.WKS,
you knew exactly what data you would later need in SCREEN.WKS, you
could have given that data a range name. Then you could use the /File
Combine Copy Named-range command to read the data you need from
DISK.WKS into SCREEN.WKS.
However, you may need data from DISK.WKS but haven't given that
data a range name. In order to get that data, you have to 1) save
SCREEN.WKS, 2) retrieve DISK.WKS and find the data, 3) give the data a
range name, 4) save DISK.WKS, 5) retrieve SCREEN.WKS, and 6) read the
necessary data from DISK.WKS into SCREEN.WKS with /File Combine Copy
Named-range.
A quicker way, though the first two steps are the same, is:
1) save SCREEN.WKS, 2) retrieve DISK.WKS and find the data, then 3) use
/File Xtract Values to put this data in a temporary file, say,
TEMP.WKS, and 5) read TEMP.WKS into SCREEN.WKS with /File Combine Copy
Entire-file. This saves time for two reasons. /File Combine will
retrieve a named range from a large file. Also, since you are not
giving DISK.WKS a new range name, you don't have to save it back to
disk.
Editor's Note: This method is quicker. However, you give up one
thing in return for speed: you haven't got a named range in DISK.WKS if
you ever need the same data again. You should give your data range
names to begin with so you never have to dump/reload the current file.

-----------------------------------------------------------------
Shrinking a Spreadsheet for Maximum Memory
(PC Magazine Vol 4 No 16 Aug 6, 1985 Spreadsheet Clinic)

If you use large 123 models, your computer may run out of memory.
One way to get memory back is to get rid of unnecessary cells. If you
decide you don't need cells beyond a certain point in the worksheet, go
to the cell just below the last useful cell and hit /Range Erase (End)
(Home) Enter. This will erase everything between the last useful cell
and the last cell in the worksheet. However, you won't get all your
memory back just because you erased the cells. If you hit (End) (Home)
again, you will find that the cursor ends up at the cell that was last
in the spreadsheet before you used /Range Erase. In other words, 123
thinks the spreadsheet is still the same size as before, even though
the cells are blank. It will release the memory it was using to store
the cell contents but will still require some memory just to keep track
of the blank cells it thinks are still part of the worksheet.
To get all your memory back do a /File Save and then a /File
Retrieve. By reading the worksheet back into memory, 123 figures out
that the blank cells really aren't part of the spreadsheet and thus
releases the memory it was using to keep track of them.
Even this technique won't work at times. Even after the /File Save
and /File Retrieve sequence, the (End) (Home) command still may take
you to a blank cell (the last one before your /Range Erase) at the
bottom of the worksheet. The reason is that some of the now-blank
cells were either unprotected or formatted with the /Range Format
command. In either case, 123 treats them as if they held data,
refusing to give them up, and so wastes memory on them.
You can solve this problem by using /Range Protect (End) (Home)
and /Range Format Reset (End) (Home). These operations disable the
special status of these cells and allow 123 to ignore them. Now,
after you have saved and retrieved the worksheet, you will find that
you have gotten all your memory back.

-----------------------------------------------------------------
Making a Table of 1-2-3 Range Addresses
(PC Magazine Vol 4 No 16 Aug 6, 1985 Spreadsheet Clinic)

Symphony has a Range Name Table command that lists the cells
represented by range names. 123 does not have this feature, but a
short macro (Figure 1) will produce the same table that Symphony
does and will sort the list of range names alphabetically.
To use the macro, first make a list of range names in a column in
your worksheet, as in the "before" part of Figure 2. Make sure that
there is an empty cell above and below the list and that there are no
empty cells within the list. When you are writing the macro, remember
to give the range name SORT to the cell where the sort routine begins.
To fill in the table with the cells in each range, put the cursor on
the first range name of the list and run the macro. This macro is a
big help in keeping track of ranges in complex worksheets. Whenever
you create a new range, add it to the list of names and rerun the
macro. This keeps the documentation current and accurate.
- - - - -
Figure 1: Macro to create a table of range names, and sort them.

\T /c~{right}~ 'Copy this label name to right
/rncCC~{bs}.{right}~ 'Name these 2 cells as Current
Cells (CC)
/xi@count(cc)=0~/xgSORT~ 'If blank, go to SORT step
{right}{edit} 'Edit duplicate label name
{home}{del}@sum({end})~ 'Convert to @sum(x) formula
{edit}{bs}{home} 'Edit again
{del}{del}{del}{del}{del}'~ 'Convert formula back to label
{left}{down} 'Go to next label name in list
/xg\T~ 'Loop back to process next
address label
SORT: {up}{end}{up} 'Position cursor for sort
/dsr 'Reset sort parameters
d{bs}.{end}{down}{right}~ 'Define data range
p~a~g 'Define sort key ...go
- - - - -
Figure 2: Printout of a list of range names in a spreadsheet before
running the macro above and after, including a display of the cell
locations.

===== BEFORE ===== ===== AFTER =====

RANGE CELLS RANGE CELLS
NAME NAME
LIST LIST
===== ===== ===== =====

SUM A A2..A16
A B C2..G2
C C D5..F13
B SUM C16

-----------------------------------------------------------------
Building a Range Name Table
(PC Magazine Vol 4 No 19 Sept 17, 1985 Spreadsheet Clinic)

If you work with large spreadsheets with many range names, it can
be hard to keep track of them. By hitting F5 and then F3, the range
names will appear in the control panel, but it can be cumbersome to
scroll through them all, looking for the one you want. A macro will
will create range names and build a table of names with a description
of the contents of each range.
When you set up the heading for the table (lines 3 and 4), it's
a good idea to make column D 16 characters wide. That lets you enter
range names as long as 123's maximum of 15 characters, with one extra
character for separation. The macro requires 3 specified range names.
Cell C3 must be called Range Names, cell E18 must be Range-Name:, and
E21 must be Scratch.
You run the macro whenever you are about to name a range. Put the
cursor on the top left cell of the range and invoke the macro with
Alt-T. The macro will prompt you you for the range name and a brief
description of its contents. It then creates the range name you
entered, and ends by letting you define the range by "pointing" with
the cursor and hitting Enter. Lines 5 and 6 are samples of the kind
of information you might put into the table. In a real worksheet you
would locate the table so as to leave many lines free for adding new
ranges. The macro:

C D E
3 Range Names Description
4 """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
5 1 SUM Sum of monthly sales figures.
6 2 NAMES Names of salesmen.
7
8
9
10
11 \T /xlEnter Range Name: ~RangeName:~
12 /xlDescription: ~Scratch~
13 /rncHere~~
14 {Goto}Range Names~{End}{down}{down}{1+{up}~{edit}{calc}~
15 {right}/CRangeName:~~{right}{/cScratch~~
16 {Goto}Here~/rndHere~
17 /rnc
18 RangeName:
19 ~
20
21 Scratch

Editor's Note: In PC Mag Vol 4 No 16 "Making a Table of 1-2-3
Range Addresses" (above), I included a different macro that builds a
table of named ranges. It is based on a routine that prints out the
cell addresses of named ranges. If this macro incorporated this
routine, the table would include a column giving the cell addresses
of each named range as well as a description line.

-----------------------------------------------------------------
Macro That Names Ranges of Unknown Lengths
(PC Magazine Vol 4 No 23 Nov 12, 1985 Spreadsheet Clinic)

You may sometimes find you have to write a 123 macro that assigns
a name to a range of cells. For example, you may need to name the
results of a data query so that you can sort or copy the output.
Most of the time this is easy: Send the cursor to the beginning
of the range and use {End}{Down}{End}{Right} to find the limits of the
range. You can run into problems, though, if the macro tries to name
a range that is only one row deep. In this case, if you go to the
first (and only) row in the range and use {End}{Down}, you could send
the cursor to the far depts of the worksheet.
The macro below solves this problem. You can use it to name a
range of only one row. For the trick to work, though, you have to
start naming the range one cell above the acual area you are interested
in. In this example, start the macro from cell C4, even though the
data query output you want to name begins in cell C5. What's in row
4 doesn't matter; it just provides space above the cells you want to
name.
The macro starts off normally. It sends the cursor to C4 and
uses END and the arrow keys to define the range. If the macro stopped
there, though, the range would include the unnecessary row above the
range. Cell D14 in the macro gets rid of this by using a technique to
move the "free cell." While you are "pointing" to define a range, each
time you hit the period key, the free cell moves clockwise to another
corner of the range. The two periods in cell D14 move the free cell
from the lower right-hand corner to the top of the range. The {Down}
command then takes row 4 out of the range, and the final carriage
return defines the range.
Although this technique works with one-row ranges, you can still
run into problems if there are no records that match your data query
and the output area is blank. Your macro may need to test the output
area to make sure it contains data before it calls this routine.
Editor's Note: As long as you are working with data query output
that is at least several columns wide, you won't have to worry about
{End}{Right} taking the cursor for a joy ride. But what happens if
your target range is just one cell? The macro can be modified to deal
with that problem, too. Just make the starting cell one column to the
left as well as one row above the raget range and change cell D14 to
read: ..{Down}{Right}~

C D E F
4 Name Address City State
5 Smith, Francis J. 127 Canton Avenue Providence RI
6
7
8
9 \N {goto}C4~ Goto row above target area
10 /rndOUTPUT~ Clear previous name use
11 /rncOUTPUT~ Create range name OUTPUT
12 {end}{down} Find lower limit
13 {end}{right} Find rightmost limit
14 ..{down}~ Move free cell clockwise to
15 top of range and move it
16 down one row to finish
17 defining the area to be
18 named

-----------------------------------------------------------------
Hiding Notes Within a Worksheet
(PC Magazine Vol 4 No 16 Aug 6, 1985 Spreadsheet Clinic)


When building 123 or Symphony models to be used by other people,
make a point of annotating them. Describe all assumptions and, when
appropriate, the rationale behind an assumption. However, if the
annotations are in the worksheet itself, the result can be visually
confusing, and if they are on paper, it may be awkward for the user
to refer to them while he is running the model. To "hide" annotations
within a worksheet, create a new column, only one character wide, to
the left of the values you want to annotate. If, when you type notes
into that column, you begin with a space or two, the column will
normally appear to be empty. When the user needs to read your notes,
however, he can move the cursor to that column, and a line of text will
appear in the control panel. If he wants to read all the text at once,
he can temporarily widen the notes column.
Editor's Note: This is the best way to annotate a worksheet,
though you may need to watch for a few things. For example, when
you're typing in a notation on the second line of the control panel,
you can use up to 80 characters. But once that note is entered, and a
user wants to read the contents by putting the cursor on that cell, the
note will appear in the top line of the control panel, which displays
only 67 characters. Hitting the F2 key will bring back all 80
characters, but not all spreadsheet users will think of that. Another
point to remember is that a long annotation will show up in any blank
cells to the right even if the adjacent cell to the right has something
in it. Let's say column A contains your notes, columns B and C contain
values, and column D is blank for aesthetic reasons. Your note text
won't appear in B or C but, if it's long enough, it will show through
in D, which is blank.

-----------------------------------------------------------------
Hiding Spreadsheet Notes
(PC Magazine Vol 4 No 23 Nov 12, 1985 Spreadsheet Clinic)

In your response to "Hiding Notes Within a Spreadsheet" (Vol 4
No 16) (above), you pointed out that if the columns to the right of the
note are blank, the note text will spill into them. You can avoid this
by starting notes with \ and as many spaces as there are characters in
the notes column. The entire spreadsheet may be blank, but you still
won't see the notes until you move the cursor to them.

-----------------------------------------------------------------
Cell Mating
(PC Magazine Vol 4 No 18 Sept 3, 1985 Spreadsheet Clinic)

This macro combines the contents of two different 123 worksheet
cells and writes them to a third cell. In the example below, the macro
(lines 3 to 33) combines the book titles in cells C37..C39 with the
authors in cells D37..D39, puts the word "by" between the two, and
writes the results to cells E37..E39. The macro works on the principle
of filling in "holes" in the macro (cells D11 and D13) with variables,
and operating on those variables when the macro processor gets to those
cells. The macro uses several predefined range names, but in every
case the range is the cell to the right of the range name in cells
C3..C33. The macro can also be used to combine strings of any kind.
Editor's Note: There is a way both to simplify the macro and to
eliminate the awkward series of {del}s (lines 14 and 15) in the process.
All you have to do to accomplish this is to remove lines 9, 14, 15, 32,
and 33, and to replace line 20 with /reEDIT~. Then give a blank cell
the range name EDIT, so as to put that part of the "work area" outside
the macro. The routine will then run a little more quickly and will
take up fewer lines.
C D E F
3 \C {goto}c37~ Cursor to first title field
4 CONTINUE /rncTITLE~~ Name the field TITLE
5 /c~TITLE_X~ Copy title to work area
6 {right} Cursor to author field
7 /c~AUTHOR_X~ Copy author to work area
8 {goto}EDIT~ Cursor to work area
9 EDIT {edit}{home} Edit work area
10 ' Label prefix
11 TITLE_X Read title
12 by Insert " by "
13 AUTHOR_X Read author
14 {del}{del}{del}{del}{del}{del} Delete "'{edit}{home} from
15 {del}{del}{del}{del}{del}{del}{del} the work area
16 ~ End edit
17 {goto}TITLE~ Cursor to TITLE
18 {right}{right} Cursor to Title/Author field
19 /cEDIT~~ Copy work area to Title/Author
20 /cEDIT_COM~EDIT~ Replace edit command
21 {goto}TITLE~ Cursor to title field
22 /rncEND_CHECK~{down}~ Create range to check for end
23 /xi(@count(END_CHECK)=1)~/xgEND~ If next title field empty, END
24 /rndEND_CHECK~ Delete range for end check
25 /rndTITLE~ Delete old TITLE
26 {down} Cursor to next title field
27 /xgCONTINUE~ Loop back to beginning
28
29 END /rndEND_CHECK~ Delete range for end check
30 /rndTITLE~ Delete old TITLE
31 /xq Quit
32
33 EDIT_COM {edit}{home} Edit command replacement
34
35
36 TITLE AUTHOR TITLE/AUTHOR
37 Rob Roy Walter Scott Rob Roy by Walter Scott
38 Burmese Days George Orwell Burmese Days by George Orwell
39 Main Street Sinclair Lewis Main Streen by Sinclair Lewis
40 Here at PC Ruth Saperstein Here at PC by Ruth Saperstein
41 Digging Eqypt Rosetta Stone Digging Egypt by Rosetta Stone
42 Moby Dick H. Melville Moby Dick by H. Melville

-----------------------------------------------------------------
1-2-3 as a Diskette Librarian
(PC Magazine Vol 4 No 19 Oct 1, 1985 Spreadsheet Clinic)

With very little work, you can turn 123 into a diskette librarian
that's better than several special-purpose programs, including IBM's
diskette librarian. All you need is to create ASCII files of your disk
directories and import them into a 123 spreadsheet. To create an ASCII
directory file, just redirect the output of the DOS DIR command to a
file on disk with the following command: DIR d:>d:filename.PRN. Don't
forget to give the file a PRN extension, because 123 won't import files
with any other extension.
Import the disk directory file with the /File Import Text command.
You will get an error message saying "Part of file is missing" when you
hit Enter. Ignore that message. Just hit Enter again and the disk
directory file will appear in your worksheet just below the cursor.
(You get the error message because the DOS pipe used to create the
directory file doesn't put a ^Z at the end.) Use the same import
command to read as many diskette directories as you like into the same
worksheet. Identify the contents of each diskette with a DOS volume
label, or give each directory in the worksheet a name, and write that
name on the diskette's paper label. You will have plenty of room to
identify each disk file within the 123 worksheet because you can put up
to 240 characters in a single cell. (If you plan to print out your
disk library, you had better limit your comments to your printer's
maximum line length.) If you keep your library up to date, you'll
never have to worry again about where you put a file.
Editor's Note: An added bonus of this technique is that you can
use 123's data commands to sort and search your disk directories.
Also, if you don't need all information in a standard directory entry
(such as the date and time when the file was last written), you can
start your remarks in a column that overlaps that information and thus
covers it up. If you decide you do need the date and time after all,
just move the cursor back to the cell containing the directory entry,
and all the original information will then appear in the first line of
the control panel.

-----------------------------------------------------------------
Word Processing with 1-2-3
(PC Magazine Vol 4 No 21 Oct 15, 1985 Spreadsheet Clinic)

With one small macro that uses the /Range Format command, you can
turn 123 into a fairly workable word processor. First, set column
width of A to 6 (or whatever you want your left margin to be). Set the
column width of B to 60 (or however many characters you want in a line
of text). Then, off out of the way, in column D or #, type the macro:
'{?}~/rj~{end}{down}/xg\W~
Name this macro \W, put the cursor in column B, hit Alt-W and go.
Type text until you have finished a paragraph and hit Enter. Your
paragraph will format itself nicely inside column B. If you type more
than 240 characters, 123 will beep, and you will have to hit Enter even
if you're not at the end of a paragraph. Just hit the edit key (F2)
and keep on going until you finish the paragraph. Hit Enter again.
Your paragraph will look fine. Hit the down-arrow key once or twice to
begin a new paragraph. Warning: if you type only one line of text and
hit Enter, you will suddenly find yourself at the bottom of the
worksheet. End-up-arrow will get you back.
While you are typing, you can use all of 123's Edit Mode commands.
You can also back up to a paragraph you wrote earlier, hit the edit
key, make changes, and hit Enter when you're finished. Your text will
even automatically realign itself to reflect the changes.
For typing address blocks, dates, titles, and so on, use the " or
^ label prefixes to right-justify or center text. When you begin a
paragraph with a number, be sure to start with ' so that 123 will know
it's a label. Also, all the normal 123 commands are still available to
you. You can Move, Copy, Erase, alphabetize, and include spreadsheet
date in your text. Just be sure to hit Ctrl-Break to get out of the
macro before you use any / commands. When you are finished with your
text, print columns A and B or write a macro that prints as much of
your worksheet as necessary.
Editor's Note: This technique can do something a lot of high-
priced word processors can't: multiple columns. If you reset column
B's width to 25 or 30, you'll get a nice, skinny column of text. Use
the /Move command to rearrange it on the page to your liking. To not
suddenly find yourself at row 2048 because of a one-line paragraph,
put an IF statement (/xi) in the macro:

\W {?}~/rj~/rncTEMP~{bs}.{down}~/xi@count(TEMP)>=2~{end}{down}/xg\w~
/xg\w~

With this modification it does an {end}{down} routine only when there
are two or more lines in a paragraph. You get some unwanted flash and
boom each time the macro executes, but his way you'll never end up
staring at a blank screen.

-----------------------------------------------------------------
Many Happy Returns
(PC Magazine Vol 4 No 21 Oct 15, 1985 Spreadsheet Clinic)

When you use 123 or Symphony, there is an easy way to look at any
part of a large spreadsheet and then return to exactly where you were
before. The trick is to hit the + key in Ready mode to make the
program think you are going to enter a formula. You can then page
around and look at whatever you like. When you're done, just hit
Escape and you'll be right back where you started. Hit Escape again
and the + sign will disappear from the command line, and leave the
original cell entry unchanged. This technique also works when you're
entering a real formula; it's designed to let you look around for any
cell addresses you might need to put into a formula. To get back to
the original cell, hit Escape only once. If you hit it again, you'll
wipe out your formula.

-----------------------------------------------------------------
Getting Rid of Unwanted Zeros
(PC Magazine Vol 4 No 21 Oct 15, 1985 Spreadsheet Clinic)

Many people use 123 to print invoices and other tabular data.
The example below is a typical sample, in which the only calculations
are #Units x Cost = Total, and an @sum of the Total column to give a
grand total.
The usual way to set up such an invoice would be to leave out the
numbers in the #Units and Cost columns, and to fill the Total column
with #Units x Cost formulas, such as +B5*D5. Until the numbers are
filled in for #Units and Cost, the Total column contains a string of
zeros. Even after you have completed the invoice, you will still have
zeros left in those rows for which there are no #Units or Cost figures
(rows 9 through 15 below). This makes for unprofessional invoices.
If you use the two-line macro below, you'll never have those
unwanted zeros again. Also, you need only one #Units x Cost formula
(+B5*D5), in cell F5. Just fill the #Units and Cost numbers in the
invoice and run the macro with Alt-C. The first line copies the
formula in F5 to the entire Total column. The second does a /Data
Query according to the ranges described in column H and eliminates all
figures in the Total column that are equal to $0.00.

A B C D E F G H
--------------------------------------------------------------
4 #UNITS | COST | TOTAL | CRITERION RANGE
5 10 | $25.00 | $250.00 | ---------------
6 5 | $5.00 | $25.00 | TOTAL
7 6 | $7.00 | $42.00 | $0.00
8 30 | $15.00 | $450.00 |
9 | | $0.00 |
10 | | $0.00 |
11 | | $0.00 | RANGES
12 | | $0.00 | ---------------
13 | | $0.00 | TOTAL = F5..F15
14 | | $0.00 | INPUT = F4..F15
15 | | $0.00 | CRIT = H6..H7
16 ----------
17 GRAND TOTAL $767.00

\C /Cf5~TOTAL~ 'Copy +B5*D5 to TOTAL column
/DQIinput~Ccrit~DDQ 'Delete data records that match $0.00

-----------------------------------------------------------------
Rows to Columns and Vice Versa
(PC Magazine Vol 4 No 21 Oct 15, 1985 Spreadsheet Clinic)

Occasionally you may have to modify a worksheet by changing the
rows into columns or the columns into rows. There are two obvious
ways to do this: 1) move or copy each cell to a new location one cell
at a time, or 2) with cells containing values, write formulas to
reproduce the values in different cells. Neither way is efficient.
Make a copy of your worksheet and put the original away. Start
123 by typing LOTUS and choose Translate from the Access System menu.
Translate your worksheet from WKS to DIF and then translate it back
from DIF to WKS. When you make this second translation, you will get
a prompt asking whether you want the translation by columns or by rows.
Choose row-wise translation, and your original columns will become rows
and your rows will become columns.
If you extract a section of the worksheet to translate and the
program hangs up, it probably means you have partial range name
definitions within the extract. Strip all range names from the extract
with the /Range Name Reset command, save the file, and try the
translation again. Translation removes range names and cell formats
anyway, so you might as well do it yourself to avoid problems.
Editor's Note: The procedure does more than remove range names
and cell formats; it also turns all formulas into values. Further, it
produces a weird sort of mirror image of the original rows and columns.
For example, a column that started in cell B4 (2 across and 4 down)
will turn into a row that begins at D2 (4 across and 2 down). Still,
if all you need is to move values and labels around, it beats any other
method.

-----------------------------------------------------------------
Two More on 1-2-3
(PC World October 1985 Star-Dot-Star)

When writing macros, users are accustomed to pressing the ESCape
key to back out of a menu. However, when ESCape is pressed within a
macro, 123 goes to whatever follows the /XM statement, causing the
macro to overwrite a cell with the macro menu labels. This can be a
serious problem if the cursor was over an important cell. To avoid
this situation, leave a blank line between the cell containing the
/XM statement and the first menu item.
Using the @SUM function is much more efficient for the use of long
addition formulas such as +A1+B5+C8+F9+H17+I15+P46+Q55+AA15+AB15. For
example, the formula @SUM(A1,B5,C8,F9,H17,I15,P46,Q55,AA15,AB15) uses
less memory as well as less disk space than it does when entered as a
string of addition.

-----------------------------------------------------------------
Entering Zip Codes
(PC Magazine Vol 4 No 22 October 29, 1985 Spreadsheet Clinic)

Entering zip codes can be a problem in 123, especially when they
begin with 0. 123 doesn't allow a numeric entry to being with a 0 and
strips it from the front of the number. This macro makes things easy:
\Z '{?}~{down}/xg\Z~
The macro automatically assigns a label prefix to your cell entry,
waits for the entry, moves to the next cell and then repeats itself.
This way zip coes can begin with 0. You get out of the endless loop
with Ctrl-Break.
Editor's Note: Zip codes become labels instead of numbers in this
case, but that should make no difference. You can still use the /Data
Sort command to sort them, and no one does airthmetic on zip codes.
This macro is handy for more than zip codes. You can use it any time
you want to enter numbers or math symbols as labels. It would be
useful for writing macros, for example, where it's easy to forget that
you have to precede every / at the beginning of a line with a label
prefix. You can modify this macro for row work by replacing {down}
with {right}. Finally, you might find it useful to replace the initial
' with ^ or " if you want to center or right-justify your labels.

-----------------------------------------------------------------
Inserting Blank Lines in a Spreadsheet
(PC Magazine Vol 4 No 23 Nov 12, 1985 Spreadsheet Clinic)

Sometimes you have to insert blank lines between the existing
lines of a spreadsheet. It's easy enough to write a macro to insert
them, but if there are many blank lines to insert, the macro will take
a long time to execute. Also, when you use the Insert command, it will
put new rows into the entire spreadsheet, whereas you may want more
space in just one (vertical) part of the worksheet and may prefer to
leave the rest of it unchanged. The figure below illustrates this
technique. Assume that you want to insert blank spaces between the
spreadsheet data in column D of the "Before" illustration; when you're
finished, the data should look like the "After" illustration. The
first step is to move any data in rows 10 through 16 of column D out
of the way to make as many blank rows as you have rows of spreadsheet
data. Next, use the /Data Fill command to enter a number series in
cells C2 to C9. Start with 1 but choose an increment of 2, so that
you get the odd number series 1 to 13. Then go End-Down to get to the
bottom of the sequence, and use /Copy. End-Up Down Return to make an
exact copy of the odd number series in cells C10 to C16. This is an
intermediate step.
Now move to C10, the first cell of the sequence just copied, and
do another /Data Fill. Hit the backspace when 123 offers the previous
data fill range and then go End-Down Return. It was the previous copy
step that let's you specify the fill range with End-Down. It's
important that the two number sequences be the same length, and this
is the easiest way to be sure that they are. 123 now proposes a data
fill starting value of 1, but enter 2 and hit Return twice. The new
sequence in cells C10 through C16 is all even numbers, and the screen
looks exactly like the "Before" illustration.
Now all you need to do is sort columns C and D with column C as
the primary sort key. The odd and even numbers in column C fall into
sequence and, in addition, the data is double-spaced, as shown in the
"After" illustration. And you haven't inserted a single new row into
the spreadsheet; only the part you sorted has changed. Thus, if you
had data in columns E and beyond, it would remain untouched.
If you need to insert two blank lines between each row of data,
use the same technique but make three data fill sequences, using step
3 instead of step 2. Start the first sequence with 1, the second with
2, and the last sequence with 3.

=== BEFORE === === AFTER ===

C D C D
3 1 Sample data 3 1 Sample data
4 3 Sample data 4 2
5 5 Sample data 5 3 Sample data
6 7 Sample data 6 4
7 9 Sample data 7 5 Sample data
8 11 Sample data 8 6
9 13 Sample data 9 7 Sample data
10 2 10 8
11 4 11 9 Sample data
12 6 12 10
13 8 13 11 Sample data
14 10 14 12
15 12 15 13 Sample data
16 14 16 14

-----------------------------------------------------------------
Preventing Escape from a Menu
(PC Magazine Vol 4 No 24 Nov 26, 1985 Spreadsheet Clinic)

With normal 123 menus it's possible to hit the Escape key to back
up through menu choices and eventually to return to the ready mode.
When you write your own menu macros, however, the Escape key will not
necessarily get you back to ready mode. Instead, it causes the macro
to skip the menu and continue on with the commands in the cell below
the /XM command.
You may not, however, want users to be able to avoid a menu choice
by hitting the Escape key, as illustrated below. The names in column E
are range names for the cells to their right. (Cell F14 has both a
macro name -- \A to start the menus -- and a range name.) The trick to
keeping the user within the menus is to put a /XG (goto) command just
below the cells that invoke menus. In this case, cells F14 and F17
contain the /XM commands that invoke the main menu and the print menu
(F4..H7 and F9..H12). If the user hits Escape after either menu
appears, the gotos in F15 and F18 will send him right back to F14,
which invokes the main menu.
The /XG commands at the ends of the menu options (cells F7, G7,
F12, G12, H11) send the user back to the main menu after the macro has
saved the file or printed the report. Thus, the only way out of the
menus -- aside from hitting Ctrl-Break -- is to exit via the last
choice in the main menu.

E F G H
4 MAIN Print Save End
5 Print the report Save the file End program
6 /xgToPrint~ /fsFILENAME~ /wey
7 /xgToMAIN~ /xgToMAIN~
8
9 Print Align Page Quit
10 Align page Page up Quit Print
11 /ppagq /pppgq /xgToMAIN~
12 /xgToMAIN~ /xgToMAIN~
13
14 ToMAIN /xmMAIN~
15 & \A /xgToMAIN~
16
17 ToPrint /xmPrint~
18 /xgToMAIN~

-----------------------------------------------------------------
Adding Sound Effects to 1-2-3 Messages
(PC Magazine Vol 4 No 24 Nov 26, 1985 Spreadsheet Clinic)

The /XL and /XN commands in the 123 macro language display a
message and wait for keyboard input. They are normally used in
interactive 123 models to request information from the user. But you
can also use them for error messages.
You can even make the computer beep in conjunction with an /XL
or /XN message simply by writing a message that is longer thatn 123's
39-character limit. The macro below shows two uses of the /XN command,
the first as a prompt for input, the second as both a prompt for input
and an error message.
In the first /XN command, the prompt message is less than 39
characters long, so the computer will not beep. In the second /XN
command, which operates only if the user makes an error, the spaces
between "Try Again" and the tilde that follows stretch the message past
the 39-character limit. The computer will beep once for each space
past 39 characters, but since the spaces follow each other immediately,
they have the effect of producing one long beep. This trick signals
errors and will not interfere with the operation of the macro in any
way.
Editor's Note: When you use /XN or /XL strictly as an error
message, 123 still waits for user input. Since /XL will accept any
kind of input, but /XN requires numbers, it's best to use /XL. In
either case, you still have to include a cell address where the user
input will be stored. The input usually doesn't matter, so you can
tuck it out of the way in a corner of the worksheet and ignore it.

-----------------------------------------------------------------
Total Solution
(PC World November 1985 Star-Dot-Star)

Often a row and a column of a worksheet should yield equal totals.
123 offers no easy way to check this, since the spreadsheet can place
only one value in a cell, either the sum of the row or the sum of the
column. The formula below compares two totals from a hypothetical
worksheet. If they match, the program displays the total; if the
values differ, you'll see "ERR".

@if(@sum(g5..g16)=@sum(B17..F17),@SUM(G5..G16),@ERR)

Change the cell references to match those of the row and column you
wish to compare.

-----------------------------------------------------------------
On Menus and Memory
(PC Magazine Vol 4 No 25 Dec 10, 1985 Spreadsheet Clinic)

If you write 123 menus with the /XM command, you will find that
many people back out of them by hitting Escape. That works fine with
123's own menus, but not always with the ones you build yourself. When
123 is processing a macro-written menu, hitting Escape just makes the
macro processor move on to the next cell in the macro. The sample menu
macros below will run differently even though the blank space in cell
D14 is the only difference between them. If you run the first macro
and then hit Escape to get out of the menu, the macro processor will
go on and read cells Dy, D7 and D8, even though they are text for the
menu rathen than macro commands. Because of the carriage return in
cell D8, the macro will write that text into whatever cell the cursor
happens to be on. If that cell had something important it it, it would
be gone. If you run the second macro and hit Escape to get out, you'll
return smoothly to the ready state. This is because the macro
processor finds a blank cell at D14 and terminates the macro.

D E F
3
4 ** First Menu **
5 /xmD6~
6 Income Balance Quit
7 Print income statement Print balance sheet Return to ready mode
8 /ppcrrINCOME~q /ppcrrBALANCE~q
9
10
11
12 ** Second Menu **
13 /xmD15~
14
15 Income Balance Quit
16 Print income statement Print balance sheet Return to ready mode
17 /ppcrrINCOME~q /ppcrrBALANCE~q

The two different formulas below do the same thing; however, you
will use less memory and disk space if you use the formula with the
@sum function. This is because 123 knows that the operator is + for
every cell in the second formula, whereas it must ust memory and disk
space to record each separate operator in the first formula.

+A1+B5+C8+F9+H17+I15+P46+Q55+AA15+AB28+BG67+BA15+CB45+N47+I16

@sum(A1,B5,C8,F9,H17,I15,P46,Q55,AA15,AB28,BG67,BA15,CB45,N47,I16)

Editor's Note: The 123 manual clearly explains that hitting
Escape when in a macro-built menu will make the macro keep executing
from the cell below the /XM command. Since the /XM command includes
the address of the cell where the menu text begins, the text doesn't
have to be right under the /XM command and thus directly in the path
of the macro. A common solution is to keep menu texts in a column
away from the macro. This allows you to include macro commands below
the /XM command that will continue to execute if the user refuses to
make a menu choice and hits Escape instead. The above solution works
well, however, and it has the advantage of keeping the menu text close
to the /XM command. This is handy when you're debugging or modifying
a macro.
A large spreadsheet which included the two different formulas was
used to test the second tip. The @sum formula took up 98 bytes of
memory, whereas the + formula took up 110 bytes, which constituted a
savings of 12 bytes. Both formulas, however, increased the
spreadsheet's disk space by exactly the same amount -- 128 bytes.

-----------------------------------------------------------------
Utility Macros
(PC Magazine Vol 4 No 26 Dec 10, 1985 Spreadsheet Clinic)


It's very common to write a formula, such as A12/B16, in which the
values in the formula depend on other cells. Occasionally one of those
cells may have the value zero. If the formula calls for multiplication
by zero, there's no problem, but if B16 turned out to be zero, the
formula would now call for division by zero and you'd get an ugly "ERR"
message. The macro:

\E {edit}{home}@if(CELLX=0,0,{end})~

is a cosmetic device to get rid of that message and display a zero
instead. Just replace CELLX with the range name or address of the
value that might be zero. The macro tests CALLX to see if it's zero
and if it's not, it leaves your formula alone. To use the macro, move
the cursor to the cell with the formula in it and hit Alt-E.
This macro speeds up the @SUM function when you want to total a
column of numbers:

\S @sum({end}{up}.{end}{up})~

Just move the cursor to a cell two rows below the column to be totaled
and hit Alt-S. If you want the total put immediately below your column
of numbers, remove the first {end} from the macro and run it with the
cursor in the cell just below the column. Note that other @functions,
like @MIN, @STD, @VAR, @AVG, and so on will work in the same macro.
This macro converts a decimal number into a percent:

\P {edit}{home}({end})*100~/rff2~~

While you can do the same thing with 123's own percent format, you
always get the % sign shown along with your number. The macro is handy
if you have a column in a spreadsheet that is title "Percentage," and
you don't want a % sign in every cell. Using the macro, all you have
to do is move to the cell you want to edit and hit Alt-P.

-----------------------------------------------------------------
Naming and Storing Macro Routines
(PC Magazine Vol 4 No 26 Dec 10, 1985 Spreadsheet Clinic)

Whenever you write a complex 123 macro, split it into separate
routines, each with a name that describes its function. You can then

call these routines from a single "main-line" macro, similar to the
subroutine calls of structured programs. After the subroutine runs,
it hands control back to the main program. It is helpful to give a
range name to the entire subroutine macro, not just to the first cell.
The operation of the macro is unchanged because a GOTO command always
takes you to the first cell anyway. But now you can use /Worksheet
Move to move the macro around within the worksheet. Even better, you
can make a library of subroutines, saving them together in one
spreadsheet under descriptive names. Whenever you need a subroutine
in your current spreadsheet, read it in from disk with /File Copy
Combine Name.

-----------------------------------------------------------------