Issue 30172 - ODFF: INDIRECT function additionally should interpret the exclamation mark as sheet-cell separator.
Summary: ODFF: INDIRECT function additionally should interpret the exclamation mark as...
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: 680m40
Hardware: All All
: P3 Trivial with 4 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: ms_interoperability, rfe_eval_ok
: 76526 (view as issue list)
Depends on:
Blocks: 71455
  Show dependency tree
Reported: 2004-06-14 09:46 UTC by daniel.rentz
Modified: 2013-08-07 15:14 UTC (History)
5 users (show)

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

patch 1. (1.40 KB, text/plain)
2008-08-26 08:56 UTC, lvyue
no flags Details
Testdocuments for Test Case Specification (16.50 KB, application/
2008-12-17 10:52 UTC, oc
no flags Details
TestCaseSpecification (6.43 KB, text/html)
2008-12-17 10:52 UTC, oc
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description daniel.rentz 2004-06-14 09:46:09 UTC
Calc's INDIRECT function additionally should interpret the exclamation mark as 
sheet-cell separator. This would improve the functionality of imported Excel 
Comment 1 ooo 2004-06-14 10:58:54 UTC
Comment 2 erwin.tenhumberg 2004-10-27 17:23:54 UTC
enhanced summary and set keywords according to RFE process
Comment 3 rpolach 2006-12-15 10:46:53 UTC
Or should be enough to automatically translate
 =INDIRECT(SUBSTITUTE("xxxx!a1"; "!"; "."))
when importing excel document.
Comment 4 ooo 2006-12-15 12:20:58 UTC
Not quite. We'll have to take into account that the sheet name may contain an
exclamation mark as well, or the reference string may actually consist of an
external reference and file name containing exclamation marks. Only the last
exclamation mark right before a cell address or range name can be converted.
Comment 5 villeroy 2006-12-15 23:06:41 UTC
Allow me to propose a workaround using a name reference(Ctrl+F3)
shSep =MID(ADDRESS(1;1;1;"Foo");4;1)
Now your formula works in both applications.
Same is possible with pattern matching:
anyChar =IF(shSep=".";".*";"*")
Notice that regular expressions are turned off when you open xls.
Tools>Options>Calc>Calculation:"Allow Regexes in Formulae"
Comment 6 jodygoldberg 2007-04-19 17:54:09 UTC
*** Issue 76526 has been marked as a duplicate of this issue. ***
Comment 7 jodygoldberg 2007-04-19 17:57:01 UTC
There are XL compatible versions in mainline now, from the R1C1 work (although
they do not handle the .de style ZnSn localization).  They'll be generally
enabled as part of the OpenFormula work rather than the current INDIRECT_XL
approach used in oo-build.

Unfortunately none of this will help with the differences in allowable
sheetnames which may cause a sheet to be renamed on import.
Comment 8 lvyue 2008-08-22 09:12:57 UTC
Hi, Eike
I have seen this issue several days,
but I can not find out a clear clue.
I feel a little confuzed.
can you point me a way to do this?
Comment 9 ooo 2008-08-25 12:37:43 UTC
@lvyue: I'd try to use a ScAddress::Convention of ScAddress::CONV_XL_A1 if the
attempts to parse with ScAddress::CONV_OOO failed. This could be done nicely in
a loop, I guess. Note that the ScAddress::CONV_XL_R1C1 case (2nd parameter not
present or not True) should not be touched by this.
Comment 10 lvyue 2008-08-26 08:56:09 UTC
Created attachment 56019 [details]
patch 1.
Comment 11 lvyue 2008-08-26 09:54:15 UTC
Hi, Eike
Thanks for your comments. :)
I thought the question too complex, I thought I should convert the string to
the format Calc uses("TabName.ColRow") before the program goes into
ScInterpreter::ScIndirect, like other formula.

And, check this patch please. :)

Comment 12 ooo 2008-09-11 20:57:31 UTC
In cws odff05:


@lvyue: The final solution looks a little bit different from the approach the
patch took, since XL_A1 style should only be tried if OOO_A1 failed, not if
XL_R1C1 was requested by the second parameter. See revision checked in.
Comment 13 ooo 2008-12-11 14:28:52 UTC
Reassigning to QA for verification.
Comment 14 oc 2008-12-17 10:52:10 UTC
Created attachment 58884 [details]
Testdocuments for Test Case Specification
Comment 15 oc 2008-12-17 10:52:40 UTC
Created attachment 58885 [details]
Comment 16 oc 2008-12-17 10:53:10 UTC
verified in internal build cws_odff05
Comment 17 2009-01-23 17:34:38 UTC
Verified in DEV300_m39 .deb version - Closing - Sophie