List all Worksheet Names Using Formula

Hi All,

Here is a method to list all sheet names in cells using formula.

Hit Ctrl + F3, New | Name : SheetNames

Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

In A2 and copied down (Prior to XL 2007),

=IF(ROWS($A$2:A2)<=COUNTA(SheetNames),INDEX(SheetN ames,ROWS($A$2:A2)),"")

XL 2007 and later:

=IFERROR(INDEX(SheetNames,ROWS($A$2:A2)),"")


Enjoy !!

Attached sample examples for pre Excel 2007 and post Excel 2003 versions.

Here's a VBA version

This is simply awesome :)

Thanks
Rajesh

Hi All,

Here is a UDF.

Code:

---

Function SHEETNAME(Optional ShtOrder As Long = 0, _
            Optional ByVal IncludeHiddenSheet As Boolean = False) As Variant
   
    '// Developed by Kris @ ExcelFox.com
   
    Dim ShtCount    As Long
    Dim i          As Long
    Dim n          As Long
   
    ShtCount = ThisWorkbook.Worksheets.Count
   
    If ShtOrder = 0 Then
        SHEETNAME = ActiveSheet.Name
        Exit Function
    End If
   
    SHEETNAME = CVErr(xlErrRef)
    Application.Volatile
   
    With ThisWorkbook
        If IncludeHiddenSheet Then
            If ShtOrder > ShtCount Then Exit Function
            SHEETNAME = .Worksheets(ShtOrder).Name
        Else
            For i = 1 To ShtCount
                If .Worksheets(i).Visible = -1 Then
                    n = n + 1
                    If n = ShtOrder Then
                        SHEETNAME = .Worksheets(i).Name
                        Exit Function
                    End If
                End If
            Next
        End If
    End With
   
End Function

---

use like

Excluding hidden sheets

In A2 and copied down,

=SHEETNAME(ROWS($A$2:A2))

Include Hidden sheet


In A2 and copied down,

=SHEETNAME(ROWS($A$2:A2),TRUE)

To get activesheet name

=SHEETNAME()


Hope this helps !

hello,

So i have a workbook with 35 sheets
im trying to make a formula which will make a list of all sheets in that workbook, in a new tab.
i would need a step-by-step explanation on how will that work, as im new in this. Thank you

In your workbook, insert a code module. Copy Admin's code from above, and paste it in the newly inserted code module. Then follow Admin's guideline on how to use this.

great function
just a little bug
I want to get rid of Ref error