Category : Lotus and other Spreadsheets
Archive   : LTSTIPS1.ZIP
Filename : DATAFILL.123

Tips on Using 1-2-3
(from Lotus News Vol 1 No 5 July 1984)

When you select /Data Fill, 123 prompts for a range to be filled,
and then asks for three values: The number with which to Start the
sequence, the number to add to that number in each Step, and the value
at which to Stop filling the range. Two facts about this command make
is useful in macros: 1) when it asks for numbers, you can type formulas
(including cell references), functions or range names so when 123 fills
the range, it evaluates the formulas and puts numbers, not formulas, in
the cells in the range; and, 2) if the fill range is just a single cell
it simply puts the value of the Start formula in that cell and ignores
the matter of Step and Stop values. In a macro, to put the value of
"FORMULA" in the cell named CELL, just type:
/dfCELL~FORMULA~~~
Note the tildes -- one after the name of the range (CELL) to get the
value, one after the formula and two more for Start and Step.

You can set up standard worksheet templates for letters and memos,
including a cell with @TODAY formatted to show the date. If you then
tell the computer the current date when you start, printed copies of
these template-produced documents will always indicate the date of
printing. But what happens if you save the newly written memo under a
different name from that of the template? No matter when you retrieve
it, you see today's date. However, the Datestamp macro offers the
ability to automatically show the date of creation. You still use a
cell formatted to show Dates, only this time you put in the value
@TODAY, not the formula. Name the cell DATE, and enter the macro:

\D /dfDATE~@TODAY~~~ Put the value of @TODAY in the cell named DATE.

Sometimes you want to stop a macro from repeating after it has
looped a particular number of times. To enable 123 to count the number
of loops, put the value for the maximum number of loops in a cell named
LIMIT, and keep the count in a cell named COUNT. Every time the macro
repeats, you want 123 to add one to COUNT, i.e., increment the count.
Next, put an /xi(if) command in the macro to stop it when COUNT > LIMIT.
Because range names can be used in formulas, /Data Fill can be useful
in this counting macro. To increment COUNT, enter:

/dfCOUNT~COUNT+1~~~ Compute the value of COUNT+1 and place it in the
cell named COUNT.

Here are the macro instructions that control the loop:

/dfCOUNT~1~~~ Start the counter at one.
LOOP [whatever macro Cell named LOOP.
instructions are
to be repeated]
/dfCOUNT~COUNT+1~~~ Add one to the counter.
/xi(COUNT>LIMIT)~/xq Stop when COUNT exceeds LIMIT.
/xgLOOP~ Otherwise, repeat the LOOP.

This is only one of many versions of a loop with a counter. You could,
for example, ask the user for the value to put in LIMIT (use the /xn
command), or have loops within loops.
The [End] key is a great help in getting around the worksheet. If
you are at the top of a column of numbers, typing [End] [Down] takes
you to the bottom of the column. If you are in an empty cell, [End]
followed by a cursor-motion key takes you to the first non-empty cell
in that direction. Two more uses of [End] are copying formulas in
adjacent columns and finding the bottom of a range.
It is common to have a formula that you want to copy adjacent to
every cell in a column, or under every cell in a row. To do so you put
the cell pointer on the formula, select /Copy, and then press [Return]
to indicate the formula to be copied. Then you point out the range to
which to copy the formula.
To use [End] to copy formulas adjacent to a column, anchor the top
of the new column of formulas with [.]. Then, using the cursor-motion
keys, put the free end of the range in the column that already has
values. Press [End] [Down] to tack down the free end of that column,
and use a cursor-motion key, usually [Right], to bring the free end into
the column you want to copy to. Since that's the right range, press
[Return] and you're done. Here's an example.
You have numbers in columns A and B, for a large number of rows.
You want to put a formula in every cell of column C, next to those
numbers. Go to the first row in column C and type the formula, say
+A1+B1. Select /Copy. Press [Return] [.]. Press [Left] [End] [Down]
[Right] [Return].
You can also use [End] to find the bottom of a range. Blank cells
can slow the process if you start from the top of the block of data, so
approach using [End] from the bottom up. To find the bottom of a
column with blank cells in it, simply page down past the end and press
[End] [Up].
Or, if a nearby column is empty, go to the end of the empty
column. Press [End] [Down] to get to the bottom of the worksheet.
Move back to the column whose end you want to locate. Press [End] [Up].