Bug 132519 - CELL("ADDRESS";...) to other sheet from Workbook imported from Excel may let INDIRECT() fail with #REF! if string reference setting forces different syntax than current formula syntax.
Summary: CELL("ADDRESS";...) to other sheet from Workbook imported from Excel may let ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-04-29 13:26 UTC by ThomasU
Modified: 2021-04-19 04:49 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel 2010 file with data added in Sheet2. Formulas then added in Sheet1 using LO 7.0.0.0dev (7.24 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-05-04 10:03 UTC, ThomasU
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ThomasU 2020-04-29 13:26:36 UTC
If one creates an Excel workbook, e.g. in Excel 2010, and then imports it into LibreOffice, then create INDIRECT() to reference a cell in another sheet of the workbook, this causes a #REF! error. If one starts with a fresh LibreOffice sheet then this bug does not occur. 
See also: https://ask.libreoffice.org/en/question/215844/syntax-for-indirect-to-access-another-sheet/?answer=241395#post-id-241395
Comment 1 m.a.riosv 2020-04-29 13:45:44 UTC
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.
Comment 2 ThomasU 2020-04-29 14:14:18 UTC
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.
Comment 3 m.a.riosv 2020-04-29 14:33:41 UTC
Because INDIRECT($Sheet2!A1) is what you want, try INDIRECT("$Sheet2!A1").
Comment 4 ThomasU 2020-04-29 15:34:39 UTC
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.
Comment 5 m.a.riosv 2020-04-29 21:05:18 UTC
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
Comment 6 ThomasU 2020-04-29 21:21:34 UTC
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.
Comment 7 m.a.riosv 2020-04-29 21:53:43 UTC
Sorry but FIXED it's used when a patch to solve the issue is done.
Comment 8 Eike Rathke 2020-04-30 13:39:13 UTC
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?
Comment 9 Eike Rathke 2020-04-30 13:59:07 UTC
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.
Comment 10 ThomasU 2020-05-04 10:03:23 UTC
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