Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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,

share|improve this question
    
You should use a macro for that. –  Mehper C. Palavuzlar Nov 23 '09 at 17:29

3 Answers 3

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

share|improve this answer

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
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.