Bug 75773 - ADDRESS function gives wrong results
Summary: ADDRESS function gives wrong results
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2014-03-04 22:01 UTC by faja
Modified: 2014-03-05 22:05 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Sample file with/without R1C1 style. (29.85 KB, application/xml)
2014-03-05 22:03 UTC, m.a.riosv
Screenshot (135.00 KB, image/png)
2014-03-05 22:05 UTC, m.a.riosv

Note You need to log in before you can comment on or make changes to this bug.
Description faja 2014-03-04 22:01:27 UTC
When using the (ADDRESS) function for referring to a cell in another sheet the Sheet name in the result is seperated from the cell specification by a dot. That should be an exclamation mark. ADRESS(1;1;1;TRUE();"Sheet1") gives "Sheet1.$A$1" and should "Sheet1!$A$1".

When subsequently using an INDIRECT function with this ddress result wil result in an error.

I would categorise this bug in EDITING.
Comment 1 m.a.riosv 2014-03-04 22:36:30 UTC
Hi faja, thanks for reporting.

Sorry, but in calc the sheet default separator is the a dot, not an exclamation mark.

You can change the option on how to see it in:
Menu/Tools/Option/LibreOffice calc/Formula/Formula options - formula syntax.
Comment 2 m.a.riosv 2014-03-05 22:03:50 UTC
Created attachment 95193 [details]
Sample file with/without R1C1 style.

El 05/03/14 22:24, Frans Rijven escribió:> hi mariosv,
> If that is so, why then does INDIRECT("Sheet1.A1") fail and gives
> ADDRESS(1,1,1,FALSE(),"Sheet1") the result: Sheet1!R1C1 (with exclamationmark) and does
> INDIRECT(ADDRESS(1,1,1,FALSE(),"Sheet1"),0) work?
> Changing the option you mentioned did not have any effect.
> greetings,
> faja

Please don't send me directly your answers, here is the place for treat the issue.

1) INDIRECT("Sheet1.A1")
   Works fine for me.

2) ADDRESS(1,1,1,FALSE(),"Sheet1") the result: Sheet1!R1C1
   Is the correct result, you are selecting the R1C1 style with the FALSE() parameter.

3) INDIRECT(ADDRESS(1,1,1,FALSE(),"Sheet1"),0)
   Similar to 2), the last parameter to 0 in the indirect function select R1C1 style.

Inner help:
Ref represents a reference to a cell or an area (in text form) for which to return the contents.
A1 (optional) - if set to 0, the R1C1 notation is used. If this parameter is absent or set to another value than 0, the A1 notation is used.

If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in INDIRECT("filename!sheetname"&B1) is not converted into the Calc address in INDIRECT("filename.sheetname"&B1).
Please also look for the help of direction.
Comment 3 m.a.riosv 2014-03-05 22:05:21 UTC
Created attachment 95196 [details]