Linking Lotus sheets

Excel and other Windows-based spreadsheets use workbooks made up of several worksheets. To access the different worksheets in a workbook you have to click a mouse on a "tab" button usually located at the bottom of the screen.

Even though this feature is not available in the version of 1-2-3 on the HP Palmtops you can simulate something like this by hooking several worksheets together with Lotus macros. The simulated "workbook" will consist of a main worksheet and several more worksheets that are hooked to this main sheet.

For example, you could have twelve different worksheets that track expenses for each month of the year. The main worksheet could contain summary data from each of the twelve worksheets.

(To review how to use the +< > operator to update cells in one worksheet from another worksheet see the Quick Tip titled "Link Files with Formulas" in the July/August, 1998 issue of The HP Palmtop Paper).

To keep things simple, the following example will use a main worksheet and a single "attached" worksheet. Once you see how the technique works you can extend it to however many extra worksheets you want.

To get started open Lotus 1-2-3 on the Palmtop and press MENU Worksheet, Column , Column range, Set-Width and set the columns A1..D1 to 14 and press Enter. Then key in the text and macros shown in Screen 1.

Screen 1: Main File Holds the Links

In cells A11, B11 and C11 be sure to press the apostrophe key before keying in \J, \S and \N otherwise you'll get cells filled with JJJJJJJJJJ, etc.. Likewise, when keying in the macros in cells A12..C13, type an apostrophe before typing / otherwise you'll activate the Lotus menu. Once you've keyed in the information, use the command MENU File Save and call the file PTP.

The three macros in the PTP worksheet are similar. The first line issues the command Menu (/) File Save ptp Enter (~) Replace. This will save this worksheet. The second line in the macros are commands to run MENU File Retrieve PTPJULY Enter (~) where PTPJULY will be our attached worksheet.

To set the macros, put the highlighting cursor in cell A11 and use the command MENU Range Name Labels Down. Use the right arrow key to highlight the cells A11 through C11 and press ENTER. (This is a shortcut for naming the macros all at once.)

Having saved the PTP worksheet, issue the command MENU Worksheet Erase Yes and key in a second worksheet shown in Screen 2.

Screen 2: Sample Macro Linked Worksheet

The macro in cells A11..A12 is almost the same as the ones in the PTP worksheet. It saves the current worksheet and retrieves the PTP worksheet. This single macro is labeled \B which stands for "Back" since it will send you back to the main worksheet.

If you add more worksheets, you can press the Fn Copy command and highlight the cells A11..A13 and press Enter to copy the text to the clipboard. You can then use the Fn+Paste keys to paste the text in another worksheet. In these other worksheets, you'll need to modify the name of the file in the /fs...~r line and you'll need to use the command MENU Range Name Label Down to set the \B macro.

When you've finished creating the second worksheet, save it with the command MENU File Save PTPJULY. Now you should be able to press the ALT+B key to run the \B macro and you'll find yourself back in the PTP worksheet. There you can press the ALT+J keys to run the \J macro which will take you to the PTPJULY worksheet.

Note that the macros do not need to be located in the cells starting at A11. They can be moved to any place in the worksheet. I've put them in these cells as a reminder that they're keyboard-macro simulations for "Tab" buttons. Likewise the column widths do not need to be set to 14. That was done to make it possible to see all the text in the macros.

Note: The .WK1 files above are available on this issue's The HP Palmtop Paper on DISK as LINK-WK1.ZIP