• Blog
  • Excel Downloads
    • Audit Tickmark Toolbar
  • Courses
  • Contact
  • Checkout

Excel Zoom

...because it's more than just a calculator


Drop Down Lists To Create Hyperlinks

May 16, 2009 by Mark 1 Comment

As discussed in a previous tip, adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format.

The example in the previous tip emphasized the importance of having consistent information when summarizing information in a PivotTable. Another use is to store lists of information, which could be anything, but in this example, I’m going to use website addresses.

Say you do a lot of research on several different topics, and summarize the results in a different spreadsheet for each topic. You could store all the bookmarks in your web browser, and chances are you already do. The problem with this is that if you’re only using a handful of bookmarks for one topic out of the many you have saved, you could end up searching through a lot of bookmarks to find the sites you need.

If you instead take the time up front to compile a list of sites you use frequently for a particular topic, you can use data validation to display them in one cell on your spreadsheet with another “clickable” cell to open the site in your web browser.

To do this, follow the steps below.

  • Select the cell where you want your list of websites to appear (for illustration purposes I’ll assume your list will appear in cell A1).
  • Click Data | Validation.
  • On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
  • In the Source box, refer to the range of cells in the same worksheet that contains your website list, or refer to a named range elsewhere in your workbook.
  • Click OK.

When you select cell A1, you should now see the list of websites when you click the arrow to the right of the cell. Now select cell B1. This is where we are going to put some text that becomes “clickable”, sort of like a link on a website.

In cell B1, type =HYPERLINK(A1,”GO”). Now whenever you change the website in cell A1, using the drop down list, you can click the words “GO” in cell B1 go to that site. It’s like having your own bookmark system in Excel!

Filed Under: Tips Tagged With: Data Validation, Name Range, Tips

Looking For More Help?

Contact us with any specific questions or feedback. We love to hear from you!

Want to level up your career? See our amazing Certificated Excel Courses and be the office Hero with your new found knowledge!

Looking for the Excel Audit Tickmark Toolbar? click here, NOW ONLY $25!

Subscribe to our mailing list
  • Facebook
  • Twitter

Search this site…

Categories

  • Macros (34)
  • Tips (67)
  • Uncategorized (23)



Copyright © 2023 · Magazine Pro Theme on Genesis Framework · WordPress · Log in

Login Form

Lost your password?