Bug 138994 - Incorrect work =FORMULA()
Summary: Incorrect work =FORMULA()
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.1.0.0.beta1+
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-12-17 14:13 UTC by Bogdan
Modified: 2020-12-18 09:38 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
example (12.40 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-12-17 14:15 UTC, Bogdan
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Bogdan 2020-12-17 14:13:13 UTC
Description:
the function FORMULA() returns an incorrect punctuation mark after tab name

Steps to Reproduce:
1.A1 is text1, B1 is text2.
2.A2 is link on A1 with tab name (like without other tab).
3.=FORMULA(A2)
4.=INDIRECT(SUBSTITUTE(FORMULA(A2);"=";""))
5.=OFFSET(INDIRECT(SUBSTITUTE(FORMULA(A2);"=";""));0;1)

Actual Results:
3: =$tab.A1
4: EROR
5: EROR

Expected Results:
3: =tab!A1
4: text1
5: text2


Reproducible: Always


User Profile Reset: No



Additional Info:
In M$ excel work correct. See attach example.
Comment 1 Bogdan 2020-12-17 14:15:47 UTC
Created attachment 168254 [details]
example
Comment 2 Mike Kaganski 2020-12-17 14:46:46 UTC
This is not a bug.

Your spreadsheet uses a mix of settings:

* Formula syntax is set to Calc A1 at Options|Calc|Formula [1];
* Reference syntax for string reference is set to Excel A1 at Options|Calc|Formula|Detailed Calculation Settings [2].

The former setting makes all formulas (including those in A2) to be in Calc syntax (with references like "$Sheet.A1"), which is correctly converted to string by FORMULA function; but then you try to use part of that string in INDIRECT, which is affected by the latter setting (and is expecting references like "Sheet!A1").

It works as intended. FORMULA is expected to return the formula as it is shown in formula bar, without any additional transformations. If you rely on Excel syntax like that, then you need to change the former setting to Excel A1.

[1] https://help.libreoffice.org/7.0/en-US/text/shared/optionen/01060900.html
[2] https://help.libreoffice.org/7.0/en-US/text/shared/optionen/detailedcalculation.html
Comment 3 Mike Kaganski 2020-12-17 14:49:29 UTC
(A note: please don't confirm your own bugs. That should be done independently.)
Comment 4 Bogdan 2020-12-18 09:38:20 UTC
Oh, ok. Thanks!
It is insidious situation.
I enable Options|Calc|Formula|Detailed Calculation Settings|default and save. After open and save in Excel this setting work in Calc, and formula works as intended also on another PC with Calc.

"A note: please don't confirm your own bugs."
Sorry. :-)