Need help creating a list of hyperlinks that link to every 61 rows

**pasqualebaldi** · 07-14-2015, 12:13 PM

For example, I have descriptions in C1:C10. I would like to turn these into hyperlinks that link to every 61 rows on the same sheet.

Example:
C1 links to A61
C2 links to A122
C3 links to A183
and so on...

Is this possible?

Thank you

**Arkadi** · 07-14-2015, 12:32 PM

How about this pasqualebaldi?

Sub linkage()
Dim ws As Worksheet
Dim i As Long
Set ws = Worksheets("Sheet2") 'change to correct sheet name
For i = 1 To 10
    ws.Hyperlinks.Add Anchor:=ws.Range("C" & i), Address:="" & ws.Range("A" & i * 61).Text & ""
Next i
End Sub

**pasqualebaldi** · 07-14-2015, 12:48 PM

I was doing a little more research and I might be able to incorporate a hyperlink match function. I've never used one before, so if I cant get it to work, then I will definitely go with this macro.

This is my function so far, but it is not working.

=HYPERLINK("#"&"Sheet2!" & ADDRESS(MATCH(Sheet1!C8,'Sheet2'!B:B),2), Sheet1!C8 )

On Sheet2 I'm trying to look up a list on Sheet1 and if any value in Sheet2 column B matches, then link to that match.

What am I doing wrong?

**ranman256** · 07-14-2015, 12:34 PM

Sub MakeHypers()
Dim i As Integer
Dim r As Long
Dim vTxt, vSht, vAddr

vSht = ActiveSheet.Name
For i = 1 To 10
   r = r + 61
   vTxt = Range("C" & i).Value
   If vTxt = "" Then vTxt = "Link"
   vAddr = vSht & "!" & "A" & r
   ActiveSheet.Hyperlinks.Add Anchor:=Range("C" & i), Address:="", SubAddress:=vAddr, TextToDisplay:=vTxt
Next
End Sub

**Arkadi** · 07-14-2015, 01:00 PM

Oh haha, my bad, you wanted to link TO the cells, not use their content...

**JohnTopley** · 07-14-2015, 02:03 PM

Try this ... replace BOOK! with your workbook name

=HYPERLINK("[Book1]Sheet2!" & ADDRESS(MATCH(Sheet1!B8,Sheet2!B:B,0),2,2,1))

**Tony Valko** · 07-14-2015, 02:09 PM

Enter this formula in D1 and copy down as needed:

=HYPERLINK("#A"&ROWS(D$1:D1)*61,"Go")

Need help creating a list of hyperlinks that link to every 61 rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need help creating a list of hyperlinks that link to every 61 rows

Re: Need help creating a list of hyperlinks that link to every 61 rows

Re: Need help creating a list of hyperlinks that link to every 61 rows

Re: Need help creating a list of hyperlinks that link to every 61 rows

Re: Need help creating a list of hyperlinks that link to every 61 rows

Re: Need help creating a list of hyperlinks that link to every 61 rows

Re: Need help creating a list of hyperlinks that link to every 61 rows

Thread Information

Users Browsing this Thread

Similar Threads

Creating bookmarks/hyperlinks to rows and columns

Creating a list of hyperlinks based on the results of a drop down box

[SOLVED] Creating a list of hyperlinks

Creating hyperlinks from Data Validation list to other sheets within xls

Creating a List of Hyperlinks

Excel 2007 : Creating a list of Hyperlinks

Creating Text Hyperlinks - why does my link icon gray out?

Bookmarks

Bookmarks

Posting Permissions