Previous Sheet

This is what you can use:

Option Explicit
Sub PreviousSheetName()
    If ActiveSheet.Name <> Sheets(1).Name Then    'checks if it isn't the first sheet (avoids the error message)
        Debug.Print ActiveSheet.Previous.Name     'print name in Immediate panel of VBE
    End If
End Sub

Then, for real use, you can valorize a variable instead of the Debug.Print and add a MsgBox if there is no previous sheet.

Sorry, no idea how this can be done with a simple formula; for sure needs at least an UDF.
Try googling "previous sheet formula excel".

Add a named range, via Name Manager, called Sheetnames
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")


The depending on excel version either of these formula.

=IFERROR(INDEX(SHEETNAMES,1,MATCH(TEXTAFTER(CELL("Filename",$A$1),"]"), SHEETNAMES,0)-1),"")
=IFERROR(INDEX(SHEETNAMES,1,MATCH(MID(CELL("Filename",$A$1),FIND("]",CELL("Filename",$A$1))+1,LEN(CELL("Filename",$A$1))), SHEETNAMES,0)-1),"")