Bug 93415

Summary:	ADDRESS function seems not caring about formula syntax parameter set to "Excel A1"
Product:	LibreOffice	Reporter:	danicotra <daniele.nicotra>
Component:	Calc	Assignee:	Eike Rathke <erack>
Status:	RESOLVED FIXED
Severity:	major	CC:	erack, Katarina.Behrens, raal, serval2412
Priority:	high
Version:	4.3.4.1 release
Hardware:	All
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=92256
Whiteboard:	target:5.3.0 target:7.2.0
Crash report or crash signature:		Regression By:

Description danicotra 2015-08-13 20:06:34 UTC

I noticed what I think must be a bug with LibreOffice Calc, here comes the fact:

1) I'm using Version 4.3.4.1

2) I went to Menu "Tools" > "Options", then "Libre Office Calc" > "Formula" and set "Formula syntax" to "Excel A1" so to make my formulas more compliant to Excel "default form" changing "." to "!" for worksheets reference separator (that means "Sheet1.A1" will become "Sheet1!A1").

3) I then put in A1 the formula: "=ADDRESS(1,1,,,"Sheet1")" and I expected something like "Sheet1!A1" as result BUT what I get is "Sheet1.$A$1" (the . is not converted to ! despite having set "Excel A1" on formula syntax) !!!

Comment 1 Julien Nabet 2015-08-14 15:16:07 UTC

Katarina: noticing your last commits (eg: http://cgit.freedesktop.org/libreoffice/core/commit/?id=2bfeabde75d1e99879465fee6fe6a97357739ae2), thought you might be interested in this one.
I didn't test but your patches didn't seem specific to INDIRECT function. In this case you'd have been killed 2 birds with one stone here, no?

Comment 2 danicotra 2016-08-16 17:34:39 UTC

I updated LibreOffice version since this bug, I originally reported for release 4.3.4.1 is still present in 5.2.5.1 release.

Comment 3 danicotra 2016-08-16 17:37:21 UTC

I updated LibreOffice version for this bug, I originally reported for release 4.3.4.1, is still present in 5.2.5.1 release.

Comment 4 danicotra 2016-08-16 17:42:26 UTC

I updated LibreOffice version for this bug, I originally reported for release 4.3.4.1, is still present in 5.1.5.2 release.

P.S.
Shall someone, please, remove my 2 previous comment? Thank you.

Comment 5 Julien Nabet 2016-08-16 18:07:54 UTC

(In reply to danicotra from comment #4)
> I updated LibreOffice version for this bug, I originally reported for
> release 4.3.4.1, is still present in 5.1.5.2 release.
> 
> P.S.
> Shall someone, please, remove my 2 previous comment? Thank you.

Version field corresponds to the earliest LO version where the bug is present.

Comment 6 Eike Rathke 2016-11-11 23:55:07 UTC

Taking.

Comment 7 Commit Notification 2016-11-11 23:57:26 UTC

Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0d6f974b97597744119db9dc3d193aeeb8f9d3fa

Resolves: tdf#93415 use proper sheet separator in ADDRESS A1 notation

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

Comment 8 Commit Notification 2021-03-04 08:05:42 UTC

Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/0effbd487da597f7a1d25a6a91af6a20090a4b3b

tdf#93415: sc_ucalc: Add unittest

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.