How do I set one cell to show the sheet name?

[ChrisVella]

Is there a formula that can give the name of the sheet where the cell containing the formula is?

[keme]

CELL(“filename”) returns filename with leading path, then “$” and sheet name.

You can extract what is after the “$” with any one of:

=MID(CELL("filename");FIND("$";CELL("filename"))+1;LEN(CELL("filename")))
=RIGHT(CELL("filename");LEN(CELL("filename"))-FIND("$";CELL("filename")))

[Lupp]

LibO version 6.2.y or higher: What about
=REGEX(CELL("filename");"(?<=#\$).*$";;1)

[keme]

I’m still mostly in the backwards universe, keep forgetting about the new functions added in LO Calc. Good job @Lupp!

[Lupp]

Not backing off from relying on a bit of user code also:

Function sheetNameByIndex_1based(pInd As Long) 
'Note: A cell will not recalculate automatically due to a change of a sheetname!
'To enforce automatic recalculation you need to make the formula depend on a volatile function.
'This can be done by appending "+NOW()*0" to the parameter expression, e.g.
doc = ThisComponent
nSheets = doc.Sheets.Count
If (pInd>0) AND (pInd<=nSheets) Then
 sheet = doc.Sheets(pInd - 1)
 REM Internal sheet index is 0 based.
 sheetNameByIndex_1based = sheet.Name
Else sheetNameByIndex_1based = ":Illegal:Index:"
End If
End Function REM sheetNameByIndex1based  

and then =SHEETNAMEBYINDEX_1BASED(SHEET())

This way you have also a tool to get the name of any sheet in the document.