Is there a formula that can give the name of the sheet where the cell containing the formula is?
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")))
LibO version 6.2.y or higher: What about
=REGEX(CELL("filename");"(?<=#\$).*$";;1)
I’m still mostly in the backwards universe, keep forgetting about the new functions added in LO Calc. Good job @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.