Today, I was approached by someone with a request that sounded very simple. She had a large Word document and she wanted to create an Excel spreadsheet in which each cell contained the name of a section in the Word document. A hyperlink in each cell should open the Word document and navigate the user to the corresponding section.
Years ago, I did something similar using Office 97 or Office 2000, so I knew it was possible.
I opened the Word document and inserted a bookmark at the top of each section. Inserting bookmarks in Word is pretty straightforward:
- Select the first line of the section
- From the menu/ribbon, select Insert Bookmark
- In the Bookmark dialog, type a name for that bookmark.
I became confused when I tried creating the hyperlinks in Excel. Inserting a hyperlink in Excel hasn't changed much through the versions:
- Type some text in a cell
- Select that cell
- From the menu/ribbon, select Insert | Hyperlink
- Find and select the file to which you want to link.
This is where I became confused. The "Insert Hyperlink" dialog contains a big button labeled "Bookmark". Naturally I clicked this button to specify the bookmark within the Word document. Unfortunately, clicking the button displayed an error. According to Excel, Word documents don't support bookmarks, although my personal experience and the on-line help says that they do.
The secret is that you should not click the bookmark button in order to link to a bookmarked location in a Word document. Rather, you should append the filename with the pound symbol ("#"), followed by the name of the bookmark to which you wish to link. For example, I wanted to link to a bookmark named "Section1" in a document named "BigWordDoc.docx", so I entered "BigWordDoc.docx#Section1", as shown below.
Apparently, the "Bookmark" button is used for cells and defined names within an Excel document.
I'm not sure if Excel's "Insert Hyperlink" dialog has changed in the last few versions, but this strikes me as a flaw in the user interface. The visual clues don't help me accomplish this task - they actually took me in a different direction.
Here is a working demo of an Excel spreadsheet with links to sections of a Word document: OfficeLinkDemo.zip (17.92 KB)