Bug 87152 - exporting function INDIRECT() from .ods to xls does not convert cell-references
Summary: exporting function INDIRECT() from .ods to xls does not convert cell-references
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.6.3 release
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-12-09 12:23 UTC by fury110
Modified: 2014-12-10 17:07 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description fury110 2014-12-09 12:23:59 UTC 
This is correct basic code for .ods:

=INDIREKT(A3&".F3")

When exporting the .ods-file to .xls this is left untouched which is wrong. It should be changed to something like the following to comply with VBA:

=INDIREKT(“‘“&A3&"‘!F4")

At least there should be a "!" instead of a dot.

Regards,
fury
Comment 1 fury110 2014-12-09 12:28:42 UTC 
btw: A3 contains the name of a calc-sheet, so A3&".F3" points to cell F3 in the sheet whose name is in cell A3 of the sheet where the formula resides.
Comment 2 raal 2014-12-09 12:56:02 UTC 
I can not confirm with LO 4.3.4, win7. Indirect function is the same in .ods and .xls. Of course when opening .xls file in excel, formula doesn't work because excel need "!" in sheet formula. This is not a bug, see https://help.libreoffice.org/Calc/Spreadsheet_Functions#INDIRECT

Setting as NEEDINFO, please test with newer version of Libreoffice. Set as unconfirmed if you have still problem with newer version.
Comment 3 fury110 2014-12-10 15:47:32 UTC 
I agree: This is no bug, it's just a little strange behaviour.

There is an option.calc.formular.formular syntax. If you set that to "Excel A1" everything is fine!

In order to make life easy for compatibility, the options's default setting could be changed accordingly...

Thanks for your quick answer & redards,
fury

btw: Is there some compatibility sheet for LibreOffice in regard to Redmond's suite which has recently been updated?
Comment 4 raal 2014-12-10 17:07:25 UTC 
(In reply to fury110 from comment #3)
> I agree: This is no bug, it's just a little strange behaviour.

Closing as NOTABUG

> 
> There is an option.calc.formular.formular syntax. If you set that to "Excel
> A1" everything is fine!
> 
> In order to make life easy for compatibility, the options's default setting
> could be changed accordingly...

but then you broke ODF compatibility, I think

> 
> Thanks for your quick answer & redards,
> fury
> 
> btw: Is there some compatibility sheet for LibreOffice in regard to
> Redmond's suite which has recently been updated?

Don't know such list