Problem
Your application requires cells to contain hyperlinks to other cells in the same spreadsheet, but the Cell.CreateHyperlink function only makes links to URLs on the internet. This article discusses a workaround using Excel’s HYPERLINK function.
Solution
Excel has a function named hyperlink() that, with some special syntax, can create links to other cells in a spreadsheet.
Normally, the hyperlink function creates a link to a URL, much like the CreateHyperlink function. Simply passing the name of a cell to the function causes it to form a bad link; Excel will interpret it as a URL.
The solution is to enclose the name of the destination cell in quotes and preface it with a pound sign. For example, a cell with formula =HYPERLINK(“#Sheet2!C3”, “Link to C3”) will contain the text “Link to C3”, and function as a hyperlink to that cell in Sheet2. The formula can be set either by editing an Excel file directly, or through setting the Cell.Formula property in ExcelWriter.
When the user clicks on the link, Excel’s focus will move to the specified cell, whether it is in the current sheet or another sheet.
Note about worksheet references:
Microsoft’s documentation on the HYPERLINK function has more information about how to create links in a spreadsheet. Careful readers will notice that their description of how to create links to cells in the same workbook is different from the one just discussed.
Namely, they recommend prefacing the sheet name with the name of the workbook in square brackets, as in =HYPERLINK(“[Book1]Sheet2!C3”, “Link to C3”). Unfortunately, this only works if you include the file extension with the name of the workbook: =HYPERLINK(“[Book1.xls]Sheet2!C3”, “Link to C3”).
Even then, this technique only works when the spreadsheet is saved to disk, not streamed to a user. The pound sign we used above is a special symbol that references the current workbook, no matter its name. Using the pound sign in your hyperlinks will let them reference the current workbook, even when streaming that workbook to the user.
Share the post "How to create hyperlinks between cells in an Excel spreadsheet"
Follow