Results 1 to 7 of 7

Thread: List all Worksheet Names Using Formula

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

    Lightbulb 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(SheetNames,ROWS($A$2:A2) ),"")

    XL 2007 and later:

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


    Enjoy !!
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Attached sample examples for pre Excel 2007 and post Excel 2003 versions.

    Here's a VBA version
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    This is simply awesome

    Thanks
    Rajesh

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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 !
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Jul 2012
    Posts
    1
    Rep Power
    0

    Unhappy how to list all the sheets in workbook

    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

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    great function
    just a little bug
    I want to get rid of Ref error

Similar Threads

  1. Print Nth Worksheet To Mth Worksheet using VBA
    By Ryan_Bernal in forum Excel Help
    Replies: 2
    Last Post: 02-28-2013, 06:57 PM
  2. Display sheet names in a message box
    By pells in forum Excel Help
    Replies: 4
    Last Post: 02-13-2013, 07:33 PM
  3. Replies: 5
    Last Post: 12-05-2012, 03:01 AM
  4. List Unique Values Using Formula
    By LalitPandey87 in forum Excel Help
    Replies: 5
    Last Post: 01-09-2012, 08:39 PM
  5. Delete Names In A Specific Worksheet
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 08-14-2011, 02:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •