MrExcel Publishing
Your One Stop for Excel Tips & Solutions

roaming hyperlink?

Posted by Paul Vordtriede on September 19, 2001 8:07 PM

Is there a way to use some type of search (VLOOKUP, ADDRESS/MATCH, etc.) to search a column of numbers on a another sheet (within the same workbook) for a specific value and return the cell address as a hyperlink?

We're using a workbook to hold information about 20,000 different samples spread across about 20 different worksheets in this workbook. We would like to create a few index pages that allows us to move down the data in a rational way.

We've designed the index pages but don't want to have to put in 20,000 hyperlinks manually. Any suggestions?


Posted by Damon Ostrander on September 24, 2001 1:48 AM

Hello Paul,

I don't know how to do this using built-in Excel functions like VLOOKUP, but it would not be difficult to write a VBA macro that does this. For example, say you have a column of cells that contain product names, and you want each cell to be linked to a corresponding product elsewhere in the workbook. So long as the product name unambiguously matches one-to-one against a corresponding product name elsewhere in the workbook so that a Find method can be used to find it, this should be fairly straightforward. Use the Add method of the Hyperlinks collection within a Worksheet to add a hyperlink to a cell.

I hope this helps.