Referencing a sheet by index number

Question

I've got a LibreOffice Calc spreadsheet that I use to keep track of my accounts receivable at work. Each sheet lists invoices and their status (paid, unpaid, etc) as well as info about each invoice. I'm trying to create a Summary sheet that lists certain data from each sheet. Creating the sheet manually is easy, but I'm trying to "automate" the process. I want the summary page to auto-update if I add a new sheet (or remove one) as I add and remove accounts to the file.

I know that LibreOffice assigns each sheet an index number that I could refer to in some sort of formula, but I cannot find a function that I can use to refer to that index number when getting a value from a cell within it. One would expect that a function like Sheet(2) would reference the second sheet, but, alas, that is not so!

I've tried using the indirect and address functions without success, but I'm not sure if I'm not understanding these functions or if they're not appropriate for what I'm trying to accomplish.

Answer 1

This has been a missing piece in Calc for a long time. The preferred solution is to write a user-defined function. Spreadsheet formulas do not access sheets by index number but Basic can.

The following function is from https://ask.libreoffice.org/en/question/16604/how-do-i-access-the-current-sheet-name-in-formula-to-use-in-indirect/.

Function SheetName(Optional nSheet)
If IsMissing(nSheet) Then
    SheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
Else
    SheetName = ThisComponent.getSheets().getByIndex(nSheet-1).getName()
EndIf
End Function

Then get a relative address of the first sheet cell A1 like this.

=ADDRESS(1,1,4,,SHEETNAME(1))

A slightly different function is given at https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=49799.