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!