You will need to get the names by the numbers of the sheets. I don’t know a way to do this by standard functions.
That is very strange in my opinion as there as well exist means to get the names (addresses) of cells by there column/row numbers as also to relatively reference them by OFFSET().
On the other hand, there are no means to reference a sheet without knowing its name.
There were threads in other forums about this, too, and even the wise man (f/m) there didn’t know a way.
A user defined function, on the other hand, can solve the problem in different ways and is really simple. One possibility:
Function SheetNameByNumber(Optional pNo As Long)
REM There seems not to exist a StandardFunction for this purpose!
REM Note: A cell will NOT RECALCULATE DUE TO A CHANGE OF THE SheetName!
REM You will have to FORCE RECALCULATION "on any event".
REM This can be done by appending "+NOW()*0" to the parameter expression, e.g.
Dim oDoc As Object
Dim oSheet As Object
Dim nSheets As Long
IF IsMissing(pNo) THEN
SheetNameByNumber = ".Parameter.Missing."
Exit Function
ENDif
oDoc = ThisComponent
nSheets = oDoc.Sheets.Count
IF (pNo > 0) AND (pNo <= nSheets) THEN
oSheet = oDoc.Sheets(pNo-1)
SheetNameByNumber = oSheet.GetName
ELSE SheetNameByNumber = ".No.Result."
ENDif
End Function REM SheetNameByNumber
Alas! There are fundamental disadventages of working with custom functions.
Example attached: SheetNames.ods
@ROSt53 (regarding the question published as an answer):
If you think so, please tell how that might work.
SHEET() will return the sequential number (starting with 1) of the sheet the referenced range or cell is placed in. No name!
ADDRESS() will return an address (notation modifyable) of a single cell on the base of row number and column number. IF the address shall explicitly point into a sheet, the NAME must be explicitly passed via a parameter.
There is one function returning a sheet name not already passed as a parameter: CELL() used with the controlling (first) parameter “address”. But: To get the name of a sheet that way we must provide a reference into the sheet as second parameter. The only function call I know returning a sheet name without basing on a reference is CELL(“filename”) without a second parameter. But: It only can return the name of the current sheet.
A list of sheet names can only be produced setting at least a reference into every sheet that shall occur.
My attached example does already demonstrate this approach in column A.
The most appropriate way to implement access to (other) sheets without knowing the name should be adding an optional parameter to OFFSET() passing the offset in sheet number.
