Excel 2010 Hyperlinking Cells

Question

I have two sheets in an excel document (workbook). They have the same number of cells in the same arrangement. The first sheet contains summary information and the second sheet (in the corresponding cell) contains the more detailed information.

I want to hyperlink the sheets so that, a person can click the summary cell to be brought to the detailed cell.

I know I can use the following formula to create a hyperlinks:

HYPERLINK(link_location, [friendly_name])

And (at least in Excel 2010) the "link_location" entry must be in the format "#BOOK!Cell" where the quotes must be included.

However, when you click on a cell to make a selection, the formula processes it simply as BOOK!CELL. Its possible to drag and have the other cells auto populate with the BOOK!CELL format. But then I just have a bunch of broken cells. Any advice on how to automatically populate my first sheet with the correct format?

Answer 1

you need to use the subaddress to link to a place in the workbook. I would run this example below which would update all links to reflect the other sheet location. Change the name of the sheets to your two sheet names.

Sub FixMyLinks()
On Error Resume Next
Dim c As Range
Dim wsSummary As Worksheet
Dim wsDetails As Worksheet

Set wsSummary = ThisWorkbook.Sheets("Sheet1")
Set wsDetails = ThisWorkbook.Sheets("Sheet2")
    For Each c In wsSummary.UsedRange.Cells
        wsSummary.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:=wsDetails.Name & "!" & Replace(c.Address, "$", ""), TextToDisplay:=c.Value
    Next c

End Sub