Yes and no?  This is almost one of those "you have to know the answer before
we will tell you the answer" type of things.  
A formula that will give you the sheet name of a sheet in the workbook is
this one (entered into a cell on 'Sheet1' and pointed to a cell in another
sheet in the workbook [Sheet3])

=MID(CELL("address",Sheet3!A1),FIND("]",CELL("address",Sheet3!A1))+1,FIND("!",CELL("address",Sheet3!A1))-FIND("]",CELL("address",Sheet3!A1))-1)

If you'll put this formula into another cell on Sheet1 you will see the need
for the FIND() functions in it:
=CELL("address",Sheet3!A1)

So you have to know the name of the other sheet before you can ask for the
name of that other sheet.  But if the name of the other sheet is changed
later, the formula will show the new name.

Another way to do this is using VB code to work through the list of sheets
in the workbook and put their names into cells in a particular workbook:

Sub ListSheetNames()
   Dim anySheet As Worksheet
   
   Worksheets("Sheet1").Select
   Range("A1").Select
   For Each anySheet In Worksheets
       ActiveCell = anySheet.Name
       ActiveCell.Offset(1, 0).Activate
   Next
End Sub

=MID(CELL("filename",Sheet2!A1), FIND("]", CELL("filename", Sheet2!A1))+ 1,
255)
will give "Sheet2" as a result.