I’ve been wondering for a long time. Considering that even the ADDRESS() function does need the NAME of another sheet to be able creating the address of a cell there …
It’s really strange that no built-in means for keeping a list of sheets or getting a name by the ordinal number seem to exist in Calc (and, I think, also not in Excel). There is a deprecated function CELL() still implemented which may help one who is disciplined enough to create a referencing formula for every new sheet in his overview range. In this case he can at least keep up with changing names without much bookkeeping.
I prefer using a very simple user defined function (Basic) for the purpose:
Function SheetName(pNo As Long)
Dim oDoc As Object
Dim oSheet As Object
Dim nSheets As Long
oDoc = ThisComponent
nSheets = oDoc.Sheets.Count()
IF (pNo > 0) AND (pNo <= nSheets) THEN
oSheet = oDoc.Sheets(pNo-1)
SheetName = oSheet.getName()
ELSE SheetName = ".No.Result."
End Function REM SheetName
For demonstration and some additional hints see attached example! To get the user defined function work will require allowing for macroe. Check the container first for malicious code. Being sure there is none, reload permitting macros.