Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Tying a Hyperlink to a Specific Cell.
Written by Allen Wyatt (last updated June 7, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
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.) When you create your hyperlink, you can then reference this named range in the Insert Hyperlink dialog box. (See Figure 1.)
Figure 1. The Insert Hyperlink dialog box.
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3466) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Tying a Hyperlink to a Specific Cell.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Does it bother you when you enter a URL and it becomes "active" as soon as you press Enter? Here's how you can turn off ...
Discover MoreIf you open workbooks in two instances of Excel, you can use drag-and-drop techniques to create hyperlinks from one ...
Discover MoreIf you have a whole slew of hyperlinks in a worksheet and you want to get rid of them, it's easier than you think. This ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-01-02 19:17:09
DAC
This was very helpful. Thank you.
2017-04-29 07:45:48
Brian Canes
Use the HYPERLINK function.
Regards
Brian
2017-04-28 03:35:36
Laurence
Indeed, I use this functionality.
Problem is that, even if I specify "A1", it seems it is transformed in "$A$1" (at least, it's what I see when I use the function "Define name" for the hyperlink. This explains why I have troubles when inserting or reordering lines, because the cell line mentioned in the hyperlink is "locked".
Isn't there a way that it takes into account what I mention in the "type the cell reference ", I mean : "§A1" only locks column A, but not the line, while "§A§1" would lock both line & colum ?
Thanks !
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Comments