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

Discuss the spreadsheet application

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

Postby sukhdev » Sat Jul 07, 2012 4:23 am

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
Last edited by Hagar Delest on Sat Jul 07, 2012 3:27 pm, edited 1 time in total.
Reason: tagged solved.
OpenOffice 3.4 on Windows XP
sukhdev
 
Posts: 49
Joined: Wed Oct 21, 2009 1:55 am

Re: How to get list of sheet names into a sheet

Postby sukhdev » Sat Jul 07, 2012 5:39 am

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
OpenOffice 3.4 on Windows XP
sukhdev
 
Posts: 49
Joined: Wed Oct 21, 2009 1:55 am

Re: How to get list of sheet names into a sheet

Postby karolus » Sat Jul 07, 2012 6:57 am

Hallo

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

Attention ! Python !
Code: Select all   Expand viewCollapse view
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
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 851
Joined: Sat Jul 02, 2011 9:47 am

Re: How to get list of sheet names into a sheet

Postby Villeroy » Sat Jul 07, 2012 8:41 am

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

F5
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26734
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to get list of sheet names into a sheet

Postby sukhdev » Sat Jul 07, 2012 12:41 pm

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
OpenOffice 3.4 on Windows XP
sukhdev
 
Posts: 49
Joined: Wed Oct 21, 2009 1:55 am

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

Postby Villeroy » Sat Jul 07, 2012 3:56 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26734
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby sukhdev » Sun Jul 08, 2012 10:30 am

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
OpenOffice 3.4 on Windows XP
sukhdev
 
Posts: 49
Joined: Wed Oct 21, 2009 1:55 am

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

Postby karolus » Sun Jul 08, 2012 10:55 am

sukhdev wrote: 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 !
AOO4, Libreoffice - 5.1 … 5.3.2.2 on Linux Mint17
User avatar
karolus
Volunteer
 
Posts: 851
Joined: Sat Jul 02, 2011 9:47 am

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

Postby Villeroy » Sun Jul 08, 2012 3:14 pm

A calculator is not a web-page editor.
Anyway: download/file.php?id=716
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26734
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby kingfisher » Mon Jul 09, 2012 12:51 am

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.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

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

Postby kurtrabbit » Wed Jan 28, 2015 5:03 pm

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.
OpenOffice 4.0.0 on Windows 7
kurtrabbit
 
Posts: 8
Joined: Wed Jan 28, 2015 4:58 pm

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

Postby acknak » Wed Jan 28, 2015 5:52 pm

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Postby kurtrabbit » Wed Jan 28, 2015 6:40 pm

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.
OpenOffice 4.0.0 on Windows 7
kurtrabbit
 
Posts: 8
Joined: Wed Jan 28, 2015 4:58 pm

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

Postby Villeroy » Wed Jan 28, 2015 7:16 pm

kurtrabbit wrote: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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26734
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby kurtrabbit » Wed Jan 28, 2015 7:40 pm

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?
OpenOffice 4.0.0 on Windows 7
kurtrabbit
 
Posts: 8
Joined: Wed Jan 28, 2015 4:58 pm

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

Postby Lupp » Wed Jan 28, 2015 7:55 pm

If you want to avoid complications with an arrayformula (total number of sheets may change ...) you can try:
Code: Select all   Expand viewCollapse view
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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2477
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 29 guests