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.
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:
-
It's simple What could be easier than pressing the Memo key and placing
a short entry in your journal as the event occurs.
-
It's fast The Palmtop's always there in your pocket. Press one button and
it's on, another and you're in Memo. This is considerably faster and more
convenient than a desktop or laptop computer, or even the typically large
stay-at-home paper journals.
-
It's powerful You not only have your journal with you, you have your PhoneBook,
Appointment Book, any number of Databases, online capability, and more.
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. 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. 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)
-
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.
-
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.
-
4. In cell B5 enter the following formula (on one line, wrapped here to
fit column width):
-
+" - "&@CHOOSE(@MOD(@DATEVALUE(A5),7),
-
"SATURDAY","SUNDAY","MONDAY"," TUESDAY","WEDNESDAY","THURSDAY","FRIDAY")
-
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.
-
5. Move your cell pointer to cell A16 and enter the following formula:
@DATEVALUE(A5).
-
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).
-
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
-
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:
-
@IF(@MOD(@DATEVALUE(A16),7)= 1,@REPEAT("*",60),@REPEAT("-",60))
-
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.
-
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
-
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.
-
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.
-
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.
-
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.
-
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).
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.