Using a 1-2-3 Worksheet to Format a Memo Document

You can use the power of the Palmtop's clipboard and 1-2-3's date and string manipulation capabilities to custom format titles and headings in Memo documents. This tutorial shows how to create and use a 1-2-3 template to set up daily titles in a journal.

By Carl Merkle

You may remember the popular TV. show "Doogie Howser, M.D." The show always ended with the young Dr. Howser writing the day's observations in his diary, a simple word processing document on a desktop PC. He could have just as easily kept his journal in Memo on the HP Palmtop, as do some Harvard Medical School students. (Harvard Medical school supplies all of its students with HP Palmtops.)

Journaling is a time-tested activity used to document personal and professional discoveries. The Palmtop is an ideal place to keep a journal because it's simple, fast, powerful:

Using Lotus 1-2-3 to format your journal entries

A journal entry usually begins with the day's date. The Palmtop's date stamp feature makes entering the date simple. However, if you want to indicate the day of the week, you have to type that in. Also, the Palmtop's date stamp only enters the current date. What if you are journaling the previous day's activities, or are planning for future dates? Again, you have to type in these dates.

I found a way around this problem by creating a template for each week's journal entries (see the template graphic beginning on this page). Instead of using Memo to create a static template, I've set my journal template up as a 1-2-3 worksheet. This lets me integrate 1-2-3's date and string manipulation capabilities to vary the dates of each entry. I open the 1-2-3 worksheet and enter the current date. The worksheet automatically adjusts the dates in the template portion of the worksheet. I then use Palmtop's clipboard to copy the template to my Memo journal file. This sets up Sunday through Saturday sections in my journal, with the appropriate date next to each section. The complete template looks like this:

********************************

09/22/96 - SUNDAY

* TODO TODAY

* ACCOMPLISHED

* MESSAGES, CALLS

* NOTES (i.e., News, Topic)

______________________

 09/23/96 - MONDAY

______________________

 09/24/96 - TUESDAY

______________________

 09/25/96 - WEDNESDAY

______________________

 09/26/96 - THURSDAY

______________________

 09/27/96 - FRIDAY

______________________

 09/28/96 - SATURDAY

* WEEK IN REVIEW

* NEXT WEEKS GOALS

In this article I walk you through the steps for creating the 1-2-3 template (called DATESTMP.WK1) and show you how to copy it to the journal. In the process of creating this template, you'll learn about some of 1-2-3's date and string manipulation capabilities, a couple of simple 1-2-3 template organization techniques, and some file organization and retrieval techniques.

Creating the 1-2-3 DATESTMP.WK1 file

You can get a copy of DATE- STMP.WK1 from the Nov/Dec 96 issue of The HP Palmtop Paper ON DISK. You can also download it from CompuServe's HPHAND Forum, Library 11, or request it from the author by mail (see details at the end of this article).

By far the quickest way to get DATESTMP.WK1 is to create it by following the steps below. This approach will teach you more about the way 1-2-3 works. The worksheet lets you enter the current date. A formula in the worksheet automatically converts the current date to the date of the nearest Sunday. This nearest-Sunday date is used in the template portion of the spreadsheet.

  1. 1. Start with an blank worksheet by pressing (MENU) Worksheet Erase Yes. Make sure the column-widths are set to 9 characters by pressing (MENU) Worksheet Global Column-Width 9 (ENTER). Save the file as DATESTMP.WK1 by pressing (MENU) File Save Datestmp (ENTER). Periodically, through this tutorial save the file by pressing (MENU) File Save (ENTER) Replace.

    2.  

     

2. Type the following labels into the indicated cells:

A1: 'INPUT SECTION:

A2: '09/21/96

B2: 'Enter today's date, [Shift][ ] [Fn][DATE]

A4: 'INTERMEDIATE CALC. SECTION:

A6: 'Note: Converts today's date to this Sunday if today

A7: 'is Saturday thru Tues. Otherwise converts today to next

A8: 'Sunday if today is Wednesday through Friday.

A10: 'OUTPUT SECTION IS RANGE NAMED "D".

A11: 'Press [Fn][Copy] and [D] [ENTER] when program

A12: 'asks "Range to copy". Then switch to Memo and press

A13: '[Fn][PASTE].

A18: '* TODO TODAY

A19: '* ACCOMPLISHED

A20: '* MESSAGES, CALLS

A21: '* NOTES (ie News, Topic)

A40: '* WEEK IN REVIEW

A41: '* NEXT WEEK'S GOALS

3. Enter the following formula in cell A5:

@CHOOSE(@MOD(@DATEVALUE(A2),7),1,0,-1,-2,4,3,2)+

 @DATEVALUE(A2)

  1. I use the DATESTMP worksheet to set up a full week of journal entries, beginning with Sunday. When I want to do this I open the template in Lotus, enter today's date in cell A2 and copy the template to Memo. The formula in cell A5 looks to the date entered in cell A2 (today's date) and outputs the date of the nearest Sunday.
  2. Lotus outputs the date in a special 1-2-3 numeric format. For example, 35330 is 1-2-3 numeric code for 09/21/96. You need to format cell A5 to display the numeric code as a recognizable date. Make sure the cellpointer is on A5, and press (MENU) Range Format Date 4 (ENTER). (Note: This article assumes you have set your date formats to display the United States date standard in 1-2-3 and on the Palmtop.
  3. 4. In cell B5 enter the following formula (on one line, wrapped here to fit column width):
  4. +" - "&@CHOOSE(@MOD(@DATEVALUE(A5),7),
  5. "SATURDAY","SUNDAY","MONDAY"," TUESDAY","WEDNESDAY","THURSDAY","FRIDAY")
  6. The formula in cell B5 uses the @CHOOSE function to display the day of the week associated with the date. After entering this formula, A5 and B5 together should now display: 09/22/96 - SUNDAY. We'll now set up the portion of the worksheet that will actually contain the weekly journal template that is copied over to Memo.
  7. 5. Move your cell pointer to cell A16 and enter the following formula:

    8.  

     

@DATEVALUE(A5).
  1. This displays the date from cell A5. Format the cell to display the date in "Long International" format by pressing (MENU) Range Format Date 4 (ENTER).
  2. 6. Move your cellpointer to cell B16. Copy the formula in cell B5 to cell B16 by pressing (MENU) Copy B5 (ENTER) (ENTER). After completing steps 5 and 6, cells A16 and B16 should now display: 09/22/96 - SUNDAY
  3. 7. Put in a line of asterisks before Sunday to separate this week of journal entries from previous weeks. Put in a line of dashes to separate each day's entries. Instead of manually entering asterisks or dashes in a row of cells, we'll put a formula in one cell that displays a row of asterisks or dashes. Move your cellpointer to cell A15 and enter the following formula:
  4. @IF(@MOD(@DATEVALUE(A16),7)= 1,@REPEAT("*",60),@REPEAT("-",60))
  5. This conditional IF formula checks to see whether the entry in cell A16 is a Sunday, if so, then A15 displays sixty stars. If A16 is not a Sunday, then the formula displays a series of sixty dashes.
  6. 8. Set up the Monday entry in your template. First copy the asterisks/ dashes formula from A15 to A23. With the cellpointer on A15 press (MENU) Copy (ENTER) A23 (ENTER). In cell A24 enter the formula: @DATEVALUE(A16)+1. Then format cell A24 as a date just as you did with cell A16 above. Next, move the cell pointer to cell B24 and copy the formula from B16 to B24 by pressing (MENU) Copy B16 (ENTER) (ENTER). Cells A24 and B24 should now display: 09/23/96 - MONDAY
  7. 9. All we need to do now is to copy the formulas in cells A23 through B24 down to create similar headings for the rest of the days in the week.
  8. A. Copy the formulas down. Move the cell pointer to cell A23. Press (MENU) Copy. 1-2-3 responds with: Copy what? A23..A23. Press the right-arrow key once and the down-arrow key once so you have selected cells A23 to B24, then press (ENTER). When 1-2-3 responds with: To where? A23, press the down-arrow key three times so 1-2-3 says: To where? A26. Then press (ENTER). Don't worry about the ERR's that show up in cells A26, A27 and B27. We'll fix this in a moment.
  9. Move the cellpointer to cell A27. The top line of the control panel should read: A27: (D4) @DATEVALUE(A19)+1. Press (F2), the {EDIT} key. The second line of the control panel should then also read: @DATEVALUE(A19)+1. Using the left arrow key, go to the part of the formula indicating cell A19 and change it to cell A24. Then press (ENTER). Cell A27 and B27 should now display: 09/24/96 - TUESDAY.
  10. B. Move the cellpointer to cell A26. Press (MENU) Copy, and copy cells A26 through B27 to cell A29. Repeat this same exercise 3 more times, copying cells A26 through B27 to cells A32, A35, and A38. At the conclusion of this exercise, cells A39 and B39 should display 09/28/96 - SATURDAY.
  11. 10. Give the template a Range Name. We need to give a name to the range of cells that display the journal template. We do this to make the process of cutting and pasting the template into the journal simpler. Move the cell pointer to cell A15. Press (MENU) Range Name Create. When 1-2-3 responds with Enter name:, type in the letter d and hit (ENTER). When 1-2-3 responds with: Enter range: A15..A15, highlight the range A15 through G41, then press (ENTER).

    12.  

     

Now try out your template by pressing (Fn) - (Copy). When 1-2-3 responds with Range to copy:, press d (the Range Name we assigned earlier) and then (ENTER). Then press the Memo key, and assuming you are in a new document press (Fn) - (Paste). You should see a weeks worth of formatted Memo set up for your use. (See screen below. You may have to adjust the margins in Memo to display the journal template properly.)

 Memo screen with DATESTMP journal template copied over. Sunday through Tuesday shown.

 Go back to 1-2-3, move the cellpointer to cell A2, and save the file. From now on, when you retrieve the file, the cellpointer should be at cell A2, ready for you to enter a date with the date stamp and set up your Memo file for the week.

On my Palmtop, I renamed DATESTAMP.WK1 as B.WK1 and placed it in the default directory in order to save time and keystrokes in opening this frequently accessed file. You may have noticed that no Lotus macros were used in this worksheet. I did this to keep things simple, but the process of formatting a Memo document could be even more automated using 1-2-3 macros or the Palmtop's system macros.

Keep it small and well organized

Memo documents, like 1-2-3 worksheets, take more memory the larger they get. It also takes you more time to find things in a large document or worksheet. Consequently, it may be desirable to keep the Memo journal document small. This can be done by limiting your journal to only one month of entries, or even one half a month. I keep all my separate journal documents in a subdirectory on my A: drive (A:\DIARY). I use a file naming convention for the documents consisting of: a two digit year number, followed by a two digit month number, followed by a three char month, followed by a number indicating which half of the month the document covers. Thus, the second file for the month of December 1996 would be called 9612DEC2 .DOC. (This naming convention keeps the files sorted chronologically in the subdirectory when viewed with FILER).

If you have your current journal file open in Memo and you want to look up an entry in a prior journal, use Filer's View option. Go to Filer, highlight the file name you want to view and press (F8). You can use the up- and DownArrow keys to page through the document. You could also accumulate these files into a big desktop file for the year and use your desktop word processing program's search capabilities to locate entries.

You might also keep your journal as a NoteTaker file. Every week could be a separate Note, with the date as the Title. You could still use the Lotus template described in this article to format a weeks worth of journal entries in the Note.

Whether you use the Palmtop, a paper notebook, or 3x5 cards, journaling is a great way to keep track of discoveries and important events in your business and personal life. It takes a surprisingly small amount of time to keep up a journal, but the rewards are great. Pull out your Palmtop, press (MEMO), and start typing.

Shareware/freeware mentioned in this article