Bug 132136 - The reference via the ADDRESS function should also work with the table number.
Summary: The reference via the ADDRESS function should also work with the table number.
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2020-04-15 21:18 UTC by Jürgen Kirsten
Modified: 2020-08-21 17:57 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Jürgen Kirsten 2020-04-15 21:18:17 UTC
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:
Comment 1 Eike Rathke 2020-08-17 10:35:42 UTC
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.
Comment 2 Jürgen Kirsten 2020-08-21 17:57:21 UTC
Function Sheetname(Optional No) As String

	On Error GoTo Errorhandler
	IF IsMissing(No) Then 
	Sheetname = ThisComponent.CurrentController.ActiveSheet.name
	Sheetname = ThisComponent.Sheets(No - 1).name
	End If
Exit Function	
	Sheetname = Error

End Function