Excel 97 hyperlinks tame large worksheets


Tip
Navigating within a complex spreadsheet has always been difficult despite features like the Edit--GoTo command, range names, sheet tabs, and macros. But Excel 97's versatile new hyperlink feature, ordinarily used to link to Web-based HTML or spreadsheet documents, may change that. Suppose you have a workbook named projectlink.xls. Its top sheet, named Summary, contains a list of expense totals. You'd like to click the value for Project X and see the expense details.
Here's how to set it up. On the sheet where you listed the Project X data, calculate the total with a regular =sum() formula. Then select Insert--Name--Define and name the Total cell ProjectXDetail. In cell B3 of the summary sheet, enter the following formula: =hyperlink("[projectlink.xls]ProjectXDetail",ProjectXDetail).
When the user clicks B3, Excel jumps to the cell named ProjectXDetail. To let the user return easily to the summary sheet, enter =hyperlink("[projectlink.xls]Summary!A1 ","Return to Summary") in a nearby cell.
The formulas assume that you're moving to another range in the same worksheet, but they work just as easily across separate worksheets. Simply substitute another file name (and path, if necessary) for projectlink.xls in the formulas.

Excel 97's hyperlinks make it easier to navigate a complex worksheet

Finally, there's a trick to editing a hyperlink formula. You can't click it without being launched to another cell. So, to select and edit a hyperlink cell, you need to right-click it, then press <Esc> to cancel the pop-up menu.
- Richard Scoville


Category: Spreadsheet
Issue: Aug 1997
Pages: 174

These Web pages are produced by Australian PC World © 1997 IDG Communications