OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.

# office message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: [OASIS Issue Tracker] Issue Comment Edited: (OFFICE-2747) 5.8 RangeReference without SheetLocator

• From: OASIS Issues Tracker <workgroup_mailer@lists.oasis-open.org>
• To: office@lists.oasis-open.org
• Date: Fri, 24 Sep 2010 13:18:25 -0400 (EDT)

```
[ http://tools.oasis-open.org/issues/browse/OFFICE-2747?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21578#action_21578 ]

Eike Rathke edited comment on OFFICE-2747 at 9/24/10 1:17 PM:
--------------------------------------------------------------

This turns out to be complicated for the range operator. All following formula expressions in cell Sheet3.A2:

Excel does not allow =SUM(INDIRECT("sheet1!a1"):INDIRECT("sheet3!b1")) and produces a #VALUE! error.
Gnumeric for =SUM(INDIRECT("sheet1!a1"):INDIRECT("sheet3!b1")) sums only the range Sheet1.A1:B1 and apparently ignores any following sheet specifier.
OOo for =SUM(INDIRECT("sheet1.a1"):INDIRECT("sheet3.b1")) sums the 3D range Sheet1.A1:Sheet3.B2

Excel =SUM(INDIRECT("sheet2!a1"):INDIRECT("sheet2!b1")) sums range Sheet2.A1:Sheet2.B1
Excel =SUM(INDIRECT("a1"):INDIRECT("sheet3!b1")) sums range Sheet3.A1:Sheet3.B1
Excel =SUM(INDIRECT("a1"):INDIRECT("sheet2!b1")) #VALUE! error.
Excel =SUM(INDIRECT("sheet2!a1"):INDIRECT("b1")) #VALUE! error.
Gnumeric =SUM(INDIRECT("sheet2!a1"):INDIRECT("sheet2!b1")) sums range Sheet2.A1:Sheet2.B1
Gnumeric =SUM(INDIRECT("a1"):INDIRECT("sheet3!b1")) sums range Sheet3.A1:Sheet3.B1
Gnumeric =SUM(INDIRECT("a1"):INDIRECT("sheet2!b1")) sums range Sheet3.A1:Sheet3.B1
Gnumeric =SUM(INDIRECT("sheet2!a1"):INDIRECT("b1")) sums range Sheet2.A1:Sheet2.B1
OOo =SUM(INDIRECT("sheet2.a1"):INDIRECT("sheet2.b1")) sums range Sheet2.A1:Sheet2.B1
OOo =SUM(INDIRECT("a1"):INDIRECT("sheet3.b1")) sums range Sheet3.A1:Sheet3.B1
OOo =SUM(INDIRECT("a1"):INDIRECT("sheet2.b1")) sums range Sheet2.A1:Sheet3.B1
OOo =SUM(INDIRECT("sheet2.a1"):INDIRECT("b1")) sums range Sheet2.A1:Sheet3.B1

I think the only specification we can give here is to say "If only one operand contains a sheet reference or both operands contain different sheet references the behavior is implementation-dependent".

was (Author: erack):
This turns out to be complicated for the range operator. All following formula expressions in cell Sheet3.A2:

Excel does not allow =SUM(INDIRECT("sheet1!a1"):INDIRECT("sheet3!b1")) and produces a #VALUE! error.
Gnumeric for =SUM(INDIRECT("sheet1!a1"):INDIRECT("sheet3!b1")) sums only the range Sheet1.A1:B1 and apparently ignores any following sheet specifier.
OOo for =SUM(INDIRECT("sheet1.a1"):INDIRECT("sheet3.b1")) sums the 3D range Sheet1.A1:Sheet3.B2

Excel =SUM(INDIRECT("sheet2!a1"):INDIRECT("sheet2!b1")) sums range Sheet2.A1:Sheet2.B1
Excel =SUM(INDIRECT("a1"):INDIRECT("sheet3!b1")) sums range Sheet3.A1:Sheet3.B1
Excel =SUM(INDIRECT("a1"):INDIRECT("sheet2!b1")) #VALUE! error.
Excel =SUM(INDIRECT("sheet2!a1"):INDIRECT("b1")) #VALUE! error.
Gnumeric =SUM(INDIRECT("sheet2!a1"):INDIRECT("sheet2!b1")) sums range Sheet2.A1:Sheet2.B1
Gnumeric =SUM(INDIRECT("a1"):INDIRECT("sheet3!b1")) sums range Sheet3.A1:Sheet3.B1
Gnumeric =SUM(INDIRECT("a1"):INDIRECT("sheet2!b1")) sums range Sheet3.A1:Sheet3.B1
Gnumeric =SUM(INDIRECT("sheet2!a1"):INDIRECT("b1")) sums range Sheet2.A1:Sheet2.B1
OOo =SUM(INDIRECT("sheet2.a1"):INDIRECT("sheet2.b1")) sums range Sheet2.A1:Sheet2.B1
OOo =SUM(INDIRECT("a1"):INDIRECT("sheet3.b1")) sums range Sheet3.A1:Sheet3.B1
OOo =SUM(INDIRECT("a1"):INDIRECT("sheet2.b1")) sums range Sheet2.A1:Sheet3.B1
OOo =SUM(INDIRECT("sheet2.a1"):INDIRECT("b1")) sums range Sheet2.A1:Sheet3.B1

> 5.8 Range Reference without SheetLocator
> ----------------------------------------
>
>                 Key: OFFICE-2747
>                 URL: http://tools.oasis-open.org/issues/browse/OFFICE-2747
>             Project: OASIS Open Document Format for Office Applications (OpenDocument) TC
>          Issue Type: Bug
>          Components: OpenFormula
>    Affects Versions: ODF 1.2 CD 05
>            Reporter: Eric Patterson
>            Assignee: Eike Rathke
>             Fix For: ODF 1.2 CD 06
>
>
> In section 5.8, there is the following text:
> If in a RangeAddress the first part (left of ':' colon) contains a SheetLocator and the second part (right of ':' colon) does not contain a SheetLocator, the second part inherits the SheetLocator from the first part.
> What if the first part does not contain a sheet locator and the second part does?

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tools.oasis-open.org/issues/secure/Administrators.jspa
-