6

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.

Lee Blake
  • 341
  • 1
  • 2
  • 15

1 Answers1

4

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.

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • 1
    Thank you! I'd seen several similar functions, but none seemed to work. This works perfectly. For anyone else using this, wrap the address() function in the indirect() function, to get the value of the specified cell. – Lee Blake Oct 11 '17 at 12:42
  • 1
    I just opened the file I used this function in, and I get a BASIC runtime error on load. It says "Property or method not found: getSheets." Once I clear out the error popups, each cell the function is used in has a !REF error. However, if I change the formula then change it back, it works again. Any ideas? – Lee Blake Oct 19 '17 at 16:06
  • I solved the issue by moving the function from the Standard module to a module specific to the file. – Lee Blake Oct 19 '17 at 17:05
  • @LeeBlake how did you do that? – Paul Grime Aug 19 '20 at 10:53