Description: With the function =INDIRECT(ADDRESS(1,1,,, "Sheet1")) I can reference the cell A1 in the worksheet "Sheet1". But if I don't know beforehand what a user of the Calc document will call the worksheet, I can't create formulas for example to collect data. It should be possible that I can also reference with the table number. For example: =INDIRECT(ADDRESS(1,1,,,1)). When I enter a text, the table name should be used, when I enter a number, just use the table number. This means the table number which I can read with =SHEET(). What else is this function for? I can't work with this table number anywhere else. It would be nice if my wish could be considered. Stay healthy! Actual Results: Expected Results: Reproducible: Always User Profile Reset: No Additional Info:
This is not how the ADDRESS() function works in any spreadsheet application so wouldn't be interoperable. Also, accepting a sheet number wouldn't work in cases where the sheet is moved to a different location or sheets were inserted in front of it. We should rather have a new function SHEETNAME() that optionally takes a reference or sheet number of which it determines the sheet name.
Function Sheetname(Optional No) As String On Error GoTo Errorhandler IF IsMissing(No) Then Sheetname = ThisComponent.CurrentController.ActiveSheet.name Else Sheetname = ThisComponent.Sheets(No - 1).name End If Exit Function Errorhandler: Sheetname = Error End Function
Unfortunately, a function SHEETNAME() has still not been introduced in LibreOffice Calc. The implementation does not seem particularly difficult. No one has yet been able to give me an answer to the question of what I should do with the sheet number that I can read out with SHEET(). Without a referencing with the SHEET number or with the help of SHEETNAME() in ADDRESS, this information is of no use to me.
As a workaround you could use =REGEX(CELL("filename";Sheet2.A1);"(?<='#\$).+")
As compared to the soulution by a BASIC UDF above workarounds -even if again supported by user code- are complicated and error-prone. There are lots of similar solutions around. Well the minimal solution isn't eligible for array-evaluation, but this should be another easy hack. Why anymore talk about workarounds? Are there stringent reasons to not create the new function? I can't see one except the need to find a "sustainable" name which is not in conflict with related functions of competitors where compatibility should not be endangedered. A conceivable alternative would be to give the SHEET() function an optional second pparameter. If it is missing (or 0) the function works as usual. With argument 1 in this position it returns the exact name of the sheet, with 2 the apostrophe-written name. The sheet itself is given then either by a reference in the first place, or by the 1-based index.
SHEET() is a standardized function, see https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#SHEET Apart from that, adding optional parameters to long introduced functions is almost always a bad idea, as it leads to compatibility problems. Adding a new SHEETNAME() function would be possible, but that would have to listen to sheet rename events to be recalculated when a sheet name changes, where currently such recalc dependency isn't implemented at all, so it's not just "let's add a new spreadsheet function". The sheet rename event listening would be needed also for CELL("filename") though, see bug 85986.
(In reply to Eike Rathke from comment #6) > Adding a new SHEETNAME() function would be possible, but that would have to > listen to sheet rename events to be recalculated when a sheet name changes, > where currently such recalc dependency isn't implemented at all, so it's not > just "let's add a new spreadsheet function". The sheet rename event > listening would be needed also for CELL("filename") though, see bug 85986. I actually thought I had valid reasons to assume SHEET_RENAME LISTENING to be implemented. Create a Calc doumcnetr with 2 sheets (Sheet1, Sheet2). Enter =Sheet2.A1. into cell Sheet1.B1. Now rename Sheet2 to Sheet2Renamed. Look at Sheet1.B1 again. From my experience: Concerning renaming sheets, moving referenced sheets/ranges by dragging with the mouse, moving referencing cells ... everything seems to be the same for sheets, rows, column with the exception that there is no indexing reference to sheets. Formulas referencing renamed sheets are updated automatically. Now I am pushed to believe that the DISPLAYED formulas per cell/array are generated from the tokens. But then it should also be efficient enough to make the new SHEETNAME() function volatile. What do I understand wrong?
Yes, displayed formulas are generated from tokens and from a sheet reference index the current sheet name is obtained. No, making a SHEETNAME() volatile isn't wanted because that would trigger recalculation on _any_ modification and we already have more of that than wanted.