Bug 132519

Not a bug, if the set up on Menu/Tools/Options/LibreOffice Calc/Formula - Detailed calculation settings - Custrom .... - Refence syntax for string reference (4 options) is done accordantly.

Hi Miguel,
Many thanks for your quick response and useful input. I think there maybe is still a bug.
Say I use in Sheet1 the CELL() function to generate the string to reference another sheet. Then CELL will return a string in the form $Sheet2.A1
However, if I use INDIRECT($Sheet2.A1) I get an error.
If I use INDIRECT($Sheet2!A1) then it works (note the exclamation mark).
So this is very very confusing! The function used to generate the needed string outputs in LibreOffice format with a dot separator but, in the context of this Excel import, the function to use the string wants Excel format with an exclamation mark separator.
So there is a kind of bug somehow. There could at least be a warning telling the user to set Options/LibreOffice Calc/Formula - Detailed calculation settings - Custom  to CalcA1. The error is really obscure to the user.

Because INDIRECT($Sheet2!A1) is what you want, try INDIRECT("$Sheet2!A1").

Hi Miguel,
I don't understand your reply.
What I highlighted is the incompatibility between CELL() and INDIRECT().
In a sheet imported from Excel into LibreOffice,
CELL() returns a $Sheet.A1 reference format
but
INDIRECT() wants a $Sheet!A1 reference format
This is incompatible.

Note also that Google Sheets uses the ! notation so one should be warned about further possible incompatibilities.

INDIRECT($Sheet2!A1) the function gets the value in $Sheet2!A1 and tries to interpreter their vallues as an address string.
For this the option is Menu/Tools/Options/LibreOffice Calc/Formula - Formula options - Formula syntax

INDIRECT("$Sheet2!A1") gets the value in $Sheet2!A1
for this one it's the already mentioned Menu/Tools/Options/LibreOffice Calc/Formula - Detailed calculation settings - Custrom .... - Refence syntax for string reference

Indirect help https://help.libreoffice.org/6.4/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3153181

Many thanks, Miguel!
I think that solves it. I will try to contribute to the documentation because this INDIRECT issue and importing from Excel causes difficulties to many.

Sorry but FIXED it's used when a patch to solve the issue is done.

Importing an Excel file should switch the option to a proper value though, i.e. "Excel A1", unless it was explicitly saved with Calc using a different value, for example

      <loext:extCalcPr stringRefSyntax="CalcA1"/>

And it does for me. So, could you attach a (redacted) sample document where it does not work?

Seeing the CELL() function mentioned in comment 2 that indeed always returns the current formula syntax and probably should follow the string reference setting if that differs from the formula syntax.

For that reopening the bug.

Created attachment 160313 [details]
Excel 2010 file with data added in Sheet2. Formulas then added in Sheet1 using LO 7.0.0.0dev

Hi Eike,

Apologies for the late reply. Here's a spreadsheet where I entered data in Sheet 2 using Excel 2010. I then used LibreOffice 7.0.0.0 dev in Linux to add formulas in Sheet 1.
I did not touch the settings in Tools / Options / Formula / Details

Many thanks for your incredible work on LibreOffice.

-Thomas

Late, but.. I do not see anything wrong with the attached document in itself. It has

      <loext:extCalcPr stringRefSyntax="CalcA1"/>

and according to that the correct reference string for INDIRECT() is "$Sheet2.A1".

If (both in LO 7.0.0 and 7.4.x) I load a .xlsx created by Excel and save it again it gets

      <loext:extCalcPr stringRefSyntax="ExcelA1"/>

just as expected, and for that document can be seen under Tools/Options/Calc/Formula/Details as well.

So however your document ended up with CalcA1 instead of ExcelA1 if you didn't touch that option I don't know.

Taking for the CELL("ADDRESS";...) part.

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

https://git.libreoffice.org/core/commit/c7bb9521ba67f52e9d665fdd24d40d8b42f0387d

Resolves: tdf#132519 Use string reference syntax for CELL("ADDRESS";...)

It will be available in 7.5.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.

Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-4":

https://git.libreoffice.org/core/commit/d700377556ca6e1adf5972fcbc8eef658adc0b53

Resolves: tdf#132519 Use string reference syntax for CELL("ADDRESS";...)

It will be available in 7.4.2.

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.

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

https://git.libreoffice.org/core/commit/3e65f33d450e512c31eeafc80956ada1cfade903

tdf#132519: sc_ucalc_formula: Add unittest

It will be available in 7.5.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.