In this tutorial we’ll take a look at several ways that you can have a Data Validation drop-down list that contains either URL links to web pages or hyperlinks to other locations in your workbook.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
The first option involves having a list of URLs in a drop-down list from which you can choose and then have it launch a web browser to that page:
The steps to do this are:
1) Create your data validation drop-down list
2) Select the one from the list you want to launch
3) While that cell is still selected, hit the “F2” function key – this puts the cell in Edit mode
4) Hit the “Enter” key
5) Click on the link and it will launch the web page
Note that when I hover over the link, it shows the destination:
However, when I choose another link, it still shows it as the previous destination, in this case “google.com”:
The key with this method is that you need to hit “F2” and “Enter” after each selection.
Now, the second option is a bit different in that, instead of converting the drop-down list selection itself into the hyperlink, we use the HYPERLINK function below the selection to “launch” that link:
The syntax for this function is: =HYPERLINK(link_location,[friendly_name])
In our scenario, the link location points to cell F1 where our drop-down list is, and the friendly name is “Launch”.
Once you select the choice from the drop-down list, click on the cell with the hyperlink function (cell F2 in our file) and it will launch that web page.
Our last scenario is a bit different in that, instead of using the drop-down list for options to web pages, we want to use it to go to various locations in our workbook. In our case, we have three choices: go to cell A1 in either Sheet1, Sheet2, or Sheet3. The sheet names are the choices in our drop-down list:
We are going to use a similar method to Option #2 above by using the HYPERLINK function below the list to launch the link. I’ve done it two ways:
Both use the friendly name as “Click Here”, but the link location are slightly different. In the top formula, I’ve entered:
=HYPERLINK(“#”&A1 & “!A1″,”Click Here”)
Here I’ve concatenated the # sign (or octothorpe) with cell A1 which is the drop-down list identifying the sheet name, then concatenating that with “!A1” which adds an exclamation point to indicate to Excel that the name chosen from the drop-down list is a sheet name, followed by the cell reference that I want that link to go to on that sheet. When resolved, the formula will look like this:
The only difference with in the lower formula is that I’ve replaced the octothorpe with the actual workbook name:
=HYPERLINK(“[Hyperlink Sheets.xlsx]”& A1 & “!A1″,”Click Here”)
Which will resolve to:
=HYPERLINK(“[Hyperlink Sheets.xlsx]Sheet1!A1″,”Click Here”)
Clicking on either “Click Here” cell will take you to cell A1 of the selected worksheet.
And that’s how you can use a Data Validation drop-down list with hyperlinks in Excel!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!