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.
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.
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.
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.
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.