CODEWITHSUNDEEP
excel
user2986959
user2986959

Reputation: 135

Excelfunction for getting sheetname to appear in a cell

I have 10 worksheets in a Excelfile and in the last sheet I want to get each sheetname in a cell.

Lets say sheet 1 is called "Black". I want this sheetname to appear in sheet 10 in a cell etc.

How can I get the sheetname into a cell with a function?

Upvotes: 0

Views: 145

Answers (2)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

You can only do exactly what you're asking using either a macro or a custom function. I'll give you the custom function:

Function:

Public Function sheetName(sheetIndex As Integer) As String
    Dim wb As Workbook

    Application.Volatile True

    Set wb = Application.Caller.Worksheet.Parent

    If sheetIndex < 1 Or sheetIndex < wb.Sheets.Count Then
        sheetName = wb.Sheets(sheetIndex).Name
    Else
        sheetName = "Index Out of Range!"
    End If
End Function

Use:

Paste the above code into a module in your workbook(if you need help with this just ask). Then use the formula: =sheetName(TheNumberOfTheSheet) where TheNumberOfTheSheet is 1 for the first sheet, 2 for the second, etc.

Upvotes: 1

pnuts
pnuts

Reputation: 59485

Once the workbook has been saved: 

=CELL("filename")

will return the full path to the sheet. If you want just the tab name it requires some string manipulation also, such as: 

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,LEN(CELL("filename")))

A Swedish version might be: 

=EXTEX(CELL("filnamn");HITTA("]";CELL("filnamn"))+1;LÄNGD(CELL("filnamn")))

Upvotes: 0

Related Questions