Tying a Hyperlink to a Specific Cell

Manoj created a hyperlink between two worksheets by using copy and paste hyperlink command (the hyperlink targets a specific cell). Later he inserted some rows on the target worksheet that caused the target cell to move down a bit. Even though the target cell moves down, the hyperlink continues to reference the old cell location. Manoj is wondering if there is a way to make sure that the hyperlink always targets the cell he intended when creating the link.

In Excel, hyperlink addresses are essentially text that references a cell. Formulas in Excel link to cell references which adjust when changes in the worksheet structure are made (inserting and deleting rows and columns, etc.). Hyperlink addresses, being text instead of cell references, will not adjust with such changes.

The solution is to create a named range that refers to the target cell you want used in the hyperlink. (You do this by choosing Insert | Name | Define or, in Excel 2007, display the Formulas tab of the ribbon and then click on Define Name in the Defined Names group.) When you create your hyperlink, you can then reference this named range in the Insert Hyperlink dialog box. (Click here to see a related figure.)

At the left of the dialog box, click Place In This Document. You'll then see a list of named ranges in your workbook and you can choose which one you want to be associated with this hyperlink. In this way, you allow Excel to take care of translating between the name and the address for that name, which means that the hyperlink will always point to the cell you want it to point to.