Issue 95868 - calc 'save as' excel .xls does not change sheet references correctly
Summary: calc 'save as' excel .xls does not change sheet references correctly
Alias: None
Product: Calc
Classification: Application
Component: save-export (show other issues)
Version: OOo 3.0
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2008-11-05 10:40 UTC by lohner
Modified: 2014-01-07 18:44 UTC (History)
2 users (show)

See Also:
Latest Confirmation in: 4.1.0-dev
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description lohner 2008-11-05 10:40:58 UTC
I exported a .ods file with a field containing
=indirect(""'sheet2'"."& "F"&COLUMN()-4)

It got saved as a .xls file and read in a german Excel installation (2003)
=indirekt("'sheet2'"."& "F"&SPALTE()-4)

The '.' needs to be a '!' for this reference to work using the sheet name.

Not sure what the fix is here, as it's inside a string and probably difficult to
find and handle correctly... but this is an incompatibility to Excel, and could
be fixed by allowing '!' as a sheet reference as well as '.'

I'd be happy to make using '!' for sheet reference a feature request to solve
this issue... otherwise, the only solution is to manually rework the
spreadsheets (ugly!).
Comment 1 Edwin Sharp 2013-05-03 11:13:52 UTC
From Help:
"If you open an Excel spreadsheet that uses indirect addresses calculated from string functions, the sheet addresses will not be translated automatically. For example, the Excel address in INDIRECT("filename!sheetname"&B1) is not converted into the Calc address in INDIRECT("filename.sheetname"&B1)."
Comment 2 Edwin Sharp 2014-01-07 18:44:54 UTC
Automatic translation is appropriate.