[Solved] Sheet tab name to cell on sheet

  • I would like to have the sheet tab name put into another sheet, say in cell A1. I have a large file which I want to summarize the names of the sheets into one sheet. And, I need the information from the sheets summarized into the same sheet. So, if sheet 1 is named Cost, then the name and the amount is listed in the summary sheet. Thanks for your help. And, by the way, this is the BEST forum I have found for this kind of information. Great Job Guys!:)

  • Neo:

    Here's a function you want to be familiar with:

    =cell("filename") ... and options for using it.

    will show your entire PATH, FILE and SHEET name as, for example:
    C:Documents and Settingscln01My DocumentsApplication Tips[Some File.xls]VLookup Example (this is the file I have set up as an example)

    =LEFT( MyFile, FIND( "[", MyFile) - 2)
    will show the PATH only, such as:
    C:Documents and Settingscln01My DocumentsApplication Tips [from the same file as above -- where I have a defined name of MyFile = cell("filename")]

    =MID( MyFile, FIND( "[", MyFile) +1, FIND( "]", MyFile) -1 - FIND( "[", MyFile))
    shows the FILE name only, or
    Some File.xls


    =RIGHT(MyFile, LEN(MyFile) - FIND("]", MyFile))
    returns the TAB name only, or
    VLookup Example

    No, these are not real simple formulas, but I have them grouped in a workbook that I can refer to (that further simplifies things with more Named Ranges), if my attachment works.


  • I just realized that I may not have answered your question, since it appears that you want to list 'other' sheet names in 'this' sheet. It's very simple. Refer to what I showed you above, and just include the other sheet by reference, such as:

    =CELL("filename", Sheet1!A1)


  • Chris is correct, but you may want something simpler to use that will perhaps be easier to use to reference sheets other than the one that's active. The following UDF should do it for you.

    Function SheetName(r As Range) As String
        'returns the sheet name of the range
        SheetName = r.Parent.Name
    End Function

    For example, Sheetname(sheet3!a1) will return Sheet3.

  • If your sheetnames are static you could use the following

    Sub ListSheetnames()
        Dim intIndex As Integer
        For intIndex = 1 To ActiveWorkbook.Sheets.Count
            ActiveCell.Offset(intIndex - 1, 0) = ActiveWorkbook.Sheets(intIndex).Name
    End Sub

    This will populate from the activecell down each sheetname in the book.
    so make sure the cells below are empty!



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!