Creating hyperlinks across a single sheet?

[CdrJameson]

Thanks for reading and many thanks for answering....I have been 'Googling' this all day,

I have got a large spreadsheet that is crying out for some hyperlinks to other locations in the spreadsheet. There are about 450 cells with unique values in the range G4:T36. These cells contain values that are used in elsewhere in the document; outside of the G4:T36 range. I have created some code that I would like to:

1. Find the matching instance of the cell value (there is only one other instance of the cell value)
2. Create a hyperlink in the original location (i.e. G4 to start with) that points to the other location of the value.
3. Repeat through G4:T36 until complete.

I have some code that doesn’t work

Sub createHyperlinks()

    For Each cell In Range("G4:T36")

        'find the other instance of the active cell in the range g4:t36
        Cells.Find(What:=ActiveCell.Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _

        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

        , SearchFormat:=False).Activate

        'add the hyperlink
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _

        "Products!ActiveCell.Address", TextToDisplay:=ActiveCell.Value

    Next

End Sub

Although it compiles and in my head should work, it doesn’t. The formatting for teh code turned out a bit screwy, hope it makes sense to you) It does find the other instance of the value but it creates a hyperlink in that location, not up in the G4:T36 range where I need it. The first value in G4 is repeated at B49, The hyperlink is created at B49. The hyperlink doesn’t work as a hyperlink, it is blue and underlined but when I select it, it throws an error ‘the reference is invalid’. When I edit the hyperlink the cell that it refers to says A1 (which is not what I wanted) but if I then type in A1 the hyperlink works.

1. How can I get the hyperlink at G4?
2. How can I get the hyperlink to work as expected?

I am an utter novice at VBA and programming in general. But the thought of manually typing 450 hyperlinks in is too much.

[JBeaucaire]

I do this all the time, but without VBA. The HYPERLINK() formula can be utilized in a adjacent cell to spot a value in another range and provide a clickable link that random spot.

3D HYPERLINK

[CdrJameson]

Thanks for the advice; I'll try it out when I get back to work on Monday.

[CdrJameson]

Well I couldn't try on Monday but I got the chance to try today, Tuesday.

Unfortunately I wasn’t able to get the formulas working to my needs. I have attached a basic spreadsheet that demonstrates roughly what I am trying to accomplish.

query_zps7f846828.png

I have manually added the hyperlinks in F3 and C10 to refer back to the parent reference, a simple task but multiplied by 500 times…

In F4 I have added the formula for a +HYPERLINK(ADDRESS(MATCH())) that is almost working. When I read up about the MATCH() function It says that it will only return the relative position of the search term. In my example the hyperlink in F4 links to A4, I understand why it addresses A4 but if I get the MATCH function to return the address of the found cell, is it possible to adapt the formula so that it returns the cell reference and hence hyperlink to B13?

Having looked at the ‘3D Hyperlink’ hyperlink I think that is maybe what I am after (I have cut and paste the example into my spreadsheet at F5), but when I try to work out what the function is doing and adapt it to my spreadsheet my head fails.

Can anyone explain and solve my issue?

I am using excel 2003 at work - if that helps anybody's answer.

[JBeaucaire]

Based off your sample sheet, the formula from my site to use was the first one.

=HYPERLINK("#" & ADDRESS(MATCH(A1, B1:B5, 0), 2), "Link")


Now, I'd suggest just using whole columns to match the ROW, the set the column to jump to. Ini C10, then copied down:
=HYPERLINK("#" & ADDRESS(MATCH(B10,$E:$E, 0), 5), "Link")

[CdrJameson]

Thanks for the tips, I (you) got it working. I dont know why but your idea to use a $B:B$ column seemed to do the trick. Whenever I used $B$4:$H$56 (for example) it behaved strangely. However after I realigned the lookup table into one column and 'look_up' to just one column it worked.

Thanks again, cant add rep again, would if I could.