[Solved] Sheet tab name to cell on sheet

Neo:

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

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

=CELL("filename")
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

and

=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.

Chris

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

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
    Next
End Sub

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

Cheers
Andy

Hi all, Just FYI :))

Worksheet Name:
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)

BookName:
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)