automatic hyperlink to worksheets in excel 2007

Question

I have a worksheet called 'summary' this contains a list of english counties, and I then have a seperate worksheet for each county.

What I want to do is automatically make my list of counties on the summary page hyperlinked to their relevent worksheet within excel.

It sounds like it should be simple... but I can't figure it out without literally clicking each name individually, hyperlink, select the worksheet from the 'within this document' box etc... it's a bit long winded.

thanks,

Answer 1

I don't have the actual formula for you, but I think you can accomplish that using the VLOOKUP function along with the HYPERLINK function.

A quick search shows a few results that would probably interest you:

vlookup hyperlink excel

Answer 2

The original macro was helpful, but needed modification to add single quotes around the target:

Do While r <= maxRows
    ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, 1), Address:="", SubAddress:="'" & Rng(r, 1).Value _
     & ")'!A1", TextToDisplay:=Rng(r, 1).Value
    On Error Resume Next
    r = r + 1
Loop

Answer 3

You could use the HYPERLINK in conjunction with the CELL function and some string functions. Or you could use the following macro:

Sub LinkToSheet()

Dim Rng As Range
Dim maxRows, r As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count 'number of rows in the selection
r = 1

Do While r <= maxRows
    ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, 1), Address:="", SubAddress:=Rng(r, 1).Value & "!A1", TextToDisplay:=Rng(r, 1).Value
    On Error Resume Next
    r = r + 1 
Loop

End Sub

Usage: Highlight all the country names in your summary sheet, press Alt+F8 and then double-click the macro. This assumes that the sheets in your list exist. The macro still creates the hyperlink even for a sheet that does not exist. It also skips blank cells. Here's a sample worksheet: http://ge.tt/2gheiw5