Hello, Excellers. Time for some hyperlink fun in Excel today. If you have been using Excel for a while you probably have used, or have been sent a spreadsheet with hyperlinks in them. These work really well until the order of sheets is changed, sheets are deleted or even new ones added. Then they don’t work as well and you will eventually get a warning that the link you want cannot be found. So, how about if you make your Excel Hyperlinks dynamic. Yes, that’s right, even if you move the sheets around, rename or add some more, your links will remain intact. Read on to find how to create dynamic hyperlinks.

The Hyperlinks Function in Excel – Basics

Let’s take a step back and cover the hyperlink function in Excel, to understand that HYPERLINK creates a shortcut that jumps to another part of the same worksheet, workbook or opens up another workbook on the internet, intranet or a network server. Once you have inserted a hyperlink in a cell and click on it, Excel will jump to the location specified.

Hyperlink Function Syntax

The syntax of the hyperlink function is

=HYPERLINK(link_location, [friendly_name])

Where

Link_Location – this is a required argument and is the file and path name to the document specified and can be another part of the same worksheet, workbook or opens up another workbook on the internet, intranet or a network server. If the link location cannot be found then an error will be displayed when you click on the hyperlink. It will look like the picture below.

Friendly_name – this is an optional argument and is the text or numeric value (known as the jump text or value) that is displayed in the cell, blue in colour and underlined. It will look a bit like the picture below. If you do not enter a friendly name ( it is an optional argument) then by default the hyperlink address will be displayed.

If the friendly name argument returns an error, for example, #value then the cell will display the error. This will be instead of the jump text or value.

The Hyperlink Problem

Just as I stated at the start of this blog post if you change the name of worksheets, move them around or even delete some then you probably will run into problems with your hyperlinks.

The Dynamic Hyperlink Solution

I did also say at the top of this blog post there is a solution. Yes, let’s make our Excel hyperlinks dynamic. It is a simple process.

• Select a cell in the worksheet that you want to link to. In this example, it will be Sheet 1 cell A1
• Enter a name for this cell. I am calling it MyRange.

• This is the same process as creating a named range. If you want to read my blog posts on named ranges click on the links below.

Formula Friday – 2 Ways To Create A Dynamic Named Range In Excel

Formula Friday – 3 Reasons To Use Named Ranges In Your Excel Formulas

• Now, we need to go back and use this named range in our hyperlink formula. Go back to where you want to place your hyperlink and you can now enter your Hyperlink formula as below

 

Now let’s test how dynamic this hyperlink really is. If we linked normally to Sheet1 cell A1 and we renamed Sheet1 as My Data, then if we clicked on the regular link it would result in an error. But, as we have made this dynamic – no problem. It still works.

 

 

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.

Do You Need Help With An Excel Problem?.

I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST.