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:
LibO version 6.2.y or higher: What about
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
This way you have also a tool to get the name of any sheet in the document.