Table of contents formula to return hyperlinks to sheets

[Penn01]

The following formula lists all worksheets in my workbook:

=IF(COUNTA(Sheets)>=ROW($A4),INDEX(Sheets,ROW($A4)),"")

[the (Sheets) reference is a named range of worksheets with the formula =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")]

I want this to also return a hyperlink to each worksheet - I have tried the below but this returns the message "cannot open the specified file" when the hyperlink is clicked. Can anyone help out on what I'm doing wrong?

=HYPERLINK(COUNTA(Sheets)>=ROW($A3),INDEX(Sheets, ROW($A3)))

Many thanks,

 

[ParamRay]

... I'm not too sure about your worksheet formulas.

You could, instead, use VBA to build a list of worksheets containing hyperlinks. Try this code:

Sub BuildContents()

    Dim Sht As Worksheet
    Dim Rng As Range
    Dim Cnt As Integer
    
    'Set cell for start of contents
    Set Rng = ThisWorkbook.Worksheets("Contents").Range("A1")   'change accordingly
    
    Cnt = 0
    For Each Sht In ThisWorkbook.Worksheets
        If Sht.Name <> Rng.Parent.Name Then
            Cnt = Cnt + 1
            Rng.Parent.Hyperlinks.Add _
                Anchor:=Rng.Offset(Cnt - 1, 0), _
                Address:=vbNullString, _
                SubAddress:=Sht.Name & "!A1", _
                TextToDisplay:=Sht.Name
        End If
    Next Sht

End Sub

 

[ABacc]

Try the below for a non-vba solution:

=IF(LEN(IF(COUNTA(Sheets)>=ROW($A3),INDEX(Sheets,ROW($A3)),"")),HYPERLINK("#"&IF(COUNTA(Sheets)>=ROW($A3),INDEX(Sheets,ROW($A3)),"")&"!A1",IF(COUNTA(Sheets)>=ROW($A3),INDEX(Sheets,ROW($A3)),"")),"""")

The link below explains this in more details:

Daily Dose of Excel » Blog Archive » Non VBA Table of contents

 

[Penn01]

Both work perfectly - many thanks for your help!