Download it now!
Bug 94975 - Function for Sheet names
Summary: Function for Sheet names
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.2.1 rc
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2015-10-12 08:21 UTC by MarMar
Modified: 2018-11-14 19:32 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
BUG 94975 (4.10 KB, image/png)
2015-10-14 21:38 UTC, marcostinez
Details

Note You need to log in before you can comment on or make changes to this bug.
Description MarMar 2015-10-12 08:21:58 UTC
Hi LO team,
I was just looking for a function in Excel to retrieve a list of all worksheet (tab)names and apparently there is none, you can only get it with VBA coding. 
Having this function is useful for making references to data in different worksheets and should update as the worksheet names change. If we can beat Excel at this it would be great.
Just curious: From my perspective as coding ignorant,this sounds like an easy thing to implement, I was shocked that Excel doesn't have it. Am I right?
Comment 1 Oliver Brinzing 2015-10-13 16:50:14 UTC
you can use the cell() function and extract the sheet name, e.g.

=MID(CELL("FILENAME");FIND("#$";CELL("FILENAME")&0*RAND())+2;256)

the rand() funtion is necessary to force lo to recalculate if, for example 
the sheet name changes
Comment 2 Buovjaga 2015-10-14 15:36:32 UTC
MarMar: so can we close this? Is the functionality in comment 1 enough for you?
Comment 3 marcostinez 2015-10-14 21:38:06 UTC
Created attachment 119622 [details]
BUG 94975
Comment 4 marcostinez 2015-10-14 21:45:26 UTC
Hi both, Thanks for your feedback. Your formula is valid to display the name of teh actual worksheet but what I meant was to continue the formula to teh next cell (vertically or horizontally) displaying all the rest of the sheeet names. 
In any case, very often the question is not IF something can be done (you have shown it is tecnically possible) but HOW EASY it is (I wonder how many people would have been able to get there without similar help).
Please forgive my non-existing coding knowledge but something like this is what I ha din mind:

=SHEETNAME(ReferenceSheet;toStart/toEnd*)

*direction of the list of sheetnames

In any case: IS it difficult to convert Oliver´s formula into a function in itself?
Comment 5 Oliver Brinzing 2015-10-17 14:16:04 UTC
i don't think it is possible to get a list of sheetnames without a macro function.
the cell() function has an optional parameter for a cellrange, but i did not find a solution to address a range via a sheet index.

please see https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=5874 for further details
Comment 6 Joel Madero 2015-10-18 18:08:33 UTC
Sounds like a valid request but a few points:

1) Since it's already possible through a macro - the functionality is questionable vs. the risk of adding yet more lines of code to the code base already at over 10,000,000 lines

2) Volunteers tackle Enhancement requests so despite moving this to NEW, there is no guarantee if/when it will ever be implemented. A volunteer developer with the skills to implement would have to find this interesting enough to actually take the time to code, test, and commit the changes. Given that this is no light task, it might never happen (or it might happen fast), impossible to predict.

Thanks for the suggestion, we'll see what happens next.