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 !