Suppose I have a very large number of sheets in my file, as indicated in the following screenshot:
What are good methods to keep an overview over them? Is it possible to arrange them on a grid or on a vertical list, so that I can view all of them and not only sheet11 to sheet30 as in my example?
The Navigator provides a list of clickable sheet names. It can be toggled on and off using F5, and it can be docked to either side of the main window. It can be configured to only show sheet names.
Great answer - it works well for me and requires no macro code. However I also really appreciate the macro code Lupp left here as well
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."
ENDif
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.