Create a Dynamic Hyperlink

Last week, I heard from Kevin Lehrbass, who runs the My Spreadsheet Lab website. Kevin has posted an Excel video on YouTube, that shows how you can make a dynamic hyperlink, using array formulas.

Select a country from a drop down list, and click on the hyperlink to go to the selected country’s largest city. Well, you’ll go to that location in the table – you won’t actually end up in the city!

Select a Country

At the top of the worksheet, Kevin created a drop down list of countries, by using data validation. You’ll start by selecting a city in that cell.

hyperlinkdynamic

Find the Largest Population

In the cells below the city, Kevin uses array formulas to find the largest population for that country, and the row in which that population is found.

hyperlinkdynamic02

If you need help with this step, you can see the instructions for MIN IF and MAX IF on my website, and watch my short video. It uses an example with office supply quantities.

minmax05

Create a Hyperlink

Next, Kevin creates a hyperlink formula that uses the row number and city name. The # sign at the start of the reference is how you link to a cell within the workbook. Click on the link to go to the selected country’s largest city. Well, you’ll go to that location in the table – you won’t actually end up in the city.

hyperlinkdynamic03

You can find more details on the HYPERLINK formula, and another short video, on my Contextures site: Excel HYPERLINK Function

Watch the Video

To see how to create the dynamic hyperlink, you can watch Kevin’s video. At the end of the video, Kevin combines the separate formulas into one large formula. You could skip that step, if you’d rather, and just hide rows 8 and 9, with the population and row formulas.

_

______________