[Solved] How to get list of sheet names into a sheet

[sukhdev]

Hello,
How do I get a list of sheet names of a spreadsheet file into cells. I want to make a contents sheet with hyperlinks to the sheets.I have seen earlier thread on how to rename sheets 1-100. This has a macro that I am trying to figure. And anyway the security warnings that come macros makes me uncomfortanle,leave alone say my boss. Is there any simpler way using inbuilt functions such as Sheet() and Sheets() etc,? Thanks.

An aside to our moderator is that I have just started compiling a list of mind bogglingly brilliant solutions I have found in this forum. I rate the spreadsheet same as the lever - you can shift paradigms!
thanks

[sukhdev]

hello,
what I have done is manually entered = in a cell and point to A1 of every sheet. Calc included the sheet name alright, eg.=Contents_2_2.A1 and dynamically updates any changes to file name. I copied the column to another place and Find and Replaced = and A1 with null to get sheet name list.

Now the problem 1) is to use =hyperlink() to open the sheet 2) automatically reference A1 in different sheets

[karolus]

Hallo

Some lines of code to set hyperlink-formulas to all sheets in doc starting at current selected cell:

Attention ! Python !

Code: Select all

def hyperlink_sheets():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.getCurrentSelection()
    scol = sel.RangeAddress.StartColumn
    srow = sel.RangeAddress.StartRow
    sheet = sel.getSpreadsheet()
    f = '''=HYPERLINK("#{0}";"{0}")'''.format
    out = [ ( f(sheetname),) for sheetname in doc.Sheets.getElementNames()]
    outrange = sheet.getCellRangeByPosition(
        scol, srow, scol, srow + len(out)-1 )
    outrange.setFormulaArray( tuple(out))
    

Karo

[Villeroy]

Now the problem 1) is to use =hyperlink() to open the sheet 2) automatically reference A1 in different sheets

F5

[sukhdev]

Good Day,

Thanks. Speechless. F5 The navigator was staring at me all the time (why aren't you using features here)! I have dragged sheet name with Drag Mode set to hyperlinks.

I searched Calc Tutorial for "Navigator", it has one example. I am looking for detailed documentation on features of Navigator with examples.

Thanks

[Villeroy]

You can use the navigator to copy references as hyperlinks and you can use it instead of hyperlinks.
There is a button to show only the selected category.
You can dock and undock the window (Ctrl+double-click grey unused area).
You can hide and unhide the docked window (click handle on the inner border) and you can temporarily unhide the docked window (click anywhere else on the inner border).
That's all, I think.

[sukhdev]

hello,

I would like to add that hyperlinks made this way can be copied to any drawn shape, such as circle, oval etc. to get rid of the usually found annoying grey background! And the font color can be changed to any desired color.

thanks

[karolus]

I would like to add that hyperlinks made this way can be copied to any drawn shape, such as circle, oval etc. to get rid of the usually found annoying grey background! And the font color can be changed to any desired color.

Nonsense !

[Villeroy]

A calculator is not a web-page editor.
Anyway: http://user.services.openoffice.org/en/ ... php?id=716

[kingfisher]

To get rid of the "annoying grey background" use the menu selection Tools > Options > Appearance. Scroll down the panel labelled "Custom colours" until you get to the heading "Text Document" and remove the tick alongside "Field shadings." From memory, that will also result in "field shadings" not showing in Writer. Alternatively, change the colour to one that suits you in both applications.

[kurtrabbit]

This is exactly what I'm trying to find out but did not actually see the answer, although it says "solved." Can someone tell me how to get a list of sheet names into a sheet. I'm trying to write a summary sheet. Thanks.

[acknak]

Welcome to the community forum!

You will need some macro code to get a literal list of sheet names. Here's the simplest workaround:

Open your document and view the summary sheet.

Open the Navigator window (View > Navigator: YES, or F5)

Expand the "Sheets" category; all sheet names should appear under "Sheets"

Click on any sheet name and drag it onto the cell grid. When you release the mouse button, a hyperlink is created pointing to the dragged sheet name.

Repeat for any other sheets you want to link to.

Of course you can also use the Navigator directly: double-click on any sheet name to switch.

[kurtrabbit]

OK, we're getting warmer, but we're not there yet. What I ultimately am trying to accomplish is this. I have 12 spreadsheets, one for each month of the year. Each month has approximately 72 tabs, each tab representing a charge customer. Each customer has multiple columns, each column a sales category. At the top of these columns is a total. In my summary page, I would like to have a list of customers (sheet tabs) on the left with a total of each of the columns (sales categories) progressing left to right. Customers come and go, so each month ends up having slightly different sheet tabs.

If I had a quick way to list the customers, I could at least go back, insert an = sign before the customer name, reference the cell with the column total and then drag off to the right to copy the remaining column totals (ie =JimJones.B5).

As it stands now, I need to go to the summary page, type the customer names in the left column and then go back and add the = sign and cell reference.

[Villeroy]

As it stands now, I need to go to the summary page, type the customer names in the left column and then go back and add the = sign and cell reference.

You should learn how to query information from well organized data sets where related data are not split across multiple sheets.

On topic "How to get list of sheet names into a sheet":

Code: Select all

Function SHEETNAMES()
SHEETNAMES = ThisComponent.Sheets.getElementNames()
End Function

The array formula =SHEETNAMES() [Ctrl+Shift+Enter] inserts a horizontal list of sheet names.
The array formula =TRANSPOSE(SHEETNAMES()) [Ctrl+Shift+Enter] inserts a vertical list of sheet names.

[kurtrabbit]

Villeroy, thank you for your reply. I have no idea what this means, "You should learn how to query information from well organized data sets where related data are not split across multiple sheets."

Also, I think this will help, "The array formula =TRANSPOSE(SHEETNAMES()) [Ctrl+Shift+Enter] inserts a vertical list of sheet names." but I have no idea what to do with the information. Inserting a list of sheet names would be very helpful but again, how do I actually do it, step-by-step?

[Lupp]

If you want to avoid complications with an arrayformula (total number of sheets may change ...) you can try:

Code: Select all

Function SheetName(Optional pNo As Long)
'This function will assume the sheets numbered starting from 1.
'Used in the first sheet of a document it may terminate 'without returning a result
'if it was called during opening the document from file.
'Note: A cell will NOT RECALCULATE DUE TO A CHANGE OF THE SheetName.
'It's the same with insertion or deletion of sheets.
'You will have to FORCE RECALCULATION "on any event".	
'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 SheetName = ".Parameter.Missing." : GoTo EmergencyExit
On Error GoTo EmergencyExit:
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
EmergencyExit:
End Function   'SheetName

Starting with =SHEETNAME(ROW(K11)-ROW(K$11)+1) in K11, e.g. and filling this formula down as far as needed, you will get your sheets survey in a column ... You may also display a subset, of course.

Keep in mind that relying on custom programming will always bring disadvantages, too.