Drop-down list of hyperlinks to different sheets in Excel

Question

I have 5 different sheets: S1, S2, S3, S4, S5. And an "Info" sheet where i have the overall information, where in every row I want a dropdown along with direct link to one of the sheets(S1, S2, S3, S4, S5). I tried using Indirect function along with Address but it is not dynamic. Is there a way of how to make a drop-down list with hyperlink to the sheet directly inbuilt with that?

I hope i could describe my situation.

Answer 1

Place a drop-down in cell, say, A5.

In B5 enter:

=HYPERLINK("#" & A5 & "!A1",A5)

This will create a "hot" hyperlink to cell A1 of the sheet you picked in the drop-down:

EDIT#1:

Place your drop-downs in column A. Each drop-down can select any worksheet. Then in the worksheet code area enter:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, sht As String, sh As Worksheet

    Set A = Range("A:A")
    If Intersect(Target, A) Is Nothing Then Exit Sub

    sht = Target.Value
    For Each sh In Sheets
        If sh.Name = sht Then
            sh.Activate
        End If
    Next sh
End Sub

whenever you change a value in column A, the code checks it the new value is a valid sheet-name. If it is, the code jumps to that sheet. If it is not a valid name, nothing bad happens.

This approach has the advantage that if you add/remove worksheets, no code change is required.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Answer 2

No need to re-invent the wheel. This is already built into Excel. Righ-click the sheet navigation arrows in the bottom left hand corner and the list of sheets will pop up. Click the sheet you want to go to. Hidden sheets (like Sheet3 in my screenshot) will no be listed.