ADVANCED USERS

Fill feature automatically creates sequential labels and saves keystrokes

Lets say you are setting up a budget and want a row or column of cells for the months of the year (i.e., January, February, March, etc..) or your companies divisions (i.e., North, South, East, West, etc...). Its a pain to continually type in this sort of repetitive data. Recent versions of Lotus 1-2-3, and other spreadsheet programs, have fill features that let you fill a range with recurring labels after you type the first entry. Well guess what? You can do the same thing on the Palmtop using the NoteTaker or DataBase application.

Lets say we want to create a fill for the months of the year, abbreviated Jan, Feb, Mar, etc.

Step 1. Create a fill list

Open a new (or existing) NoteTaker file. Press (F2) to add an entry and key in the list type in the Title field (i.e., MonthsList). Tab to the Note field and type in your fill sequence, one month per line. Jan Feb Mar etc.

2. Copy the months fill list to the Palmtops clipboard.

In the Notes field of the MonthsList, highlight the entire list of months. (Move the cursor to the letter J in Jan.), hold down (SHIFT) and press (DownArrow) till the list is highlighted. Then press (Fn)-(=) to copy the list of labels to the clipboard.

3. Copy the fill list to the Lotus spreadsheet.

Open Lotus 1-2-3 to an empty spreadsheet, move the cursor to the cell in which you want Jan to appear (say, A2) and press (Fn)-(+) to paste the column of months into your spreadsheet (Jan in A2, Feb in A3, Mar in A4, etc.).

4. Display as row instead of column.

Lets say you have the months in cells A2 through A13. If you want your list to display as a row (e.g., along the top, with Jan in A2, Feb in B2, Mar in C2, etc.) follow these instructions:

  1. A. Press (MENU) Range Transpose and you're asked Transpose what? A2..A13. Press (ENTER), and you're asked To where?

  2. B. Type in the starting cell of the new location of the months (e.g., A2) and press (ENTER).

The months Jan through Dec will be copied along the top of your spreadsheet, in cells A2 through L2. This process does not delete the contents of the old cells. You'll have to do that on your own.

Create a 1-2-3 formula library in NoteTaker

You can store your favorite 1-2-3 formulas as well as fill lists in NoteTaker or DataBase. From there, you can Cut and Paste them into spreadsheets as needed, without having to re-enter them. For example, I commonly use the following formula to tell me the day-of-the-week based on a date entered in cell A2:

@CHOOSE(@MOD(@DATEVALUE (A2),7),SATURDAY,SUNDAY, MONDAY,TUESDAY,WEDNESDAY, THURSDAY,FRIDAY).

To save the formula as a NoteTaker entry, I did the following:

  1. 1. Open the spreadsheet and highlight the cell with the desired formula in it.

  2. 2. Press (MENU) Range Format Text to display the cells contents as the formula in it (instead of the formulas result).

  3. 3. Press (MENU) Worksheet Column Set-Width and widen or shrink the column width so it displays the entire formula.

  4. 4. Press (Fn)-(COPY) to copy the formula to the clipboard.

  5. 5. Add a new NoteTaker entry and paste the formula into the Note field.

Now, when you want to use the formula again in another spreadsheet, just use the clipboard to transfer it from NoteTaker to 1-2-3. Then edit it to remove the leading apostrophe (), and your formula should work. You can also use this same technique to save macro instructions you may want to use in multiple spreadsheets.