RE: [office-formula] 3-d references

[ericpa@microsoft.com]

I could have been more clear in my first message.

My questions were based on how Excel behaves today, so let me start there a= s background information.

Excel 3-D references
--------------------
A. Excel 3-D references consist of a range of sheets, followed by an exclam= ation point and then a cell range reference.  It looks like this: =3DSUM(Sh=
eet1:Sheet2!A1:B5)

B.  The formula above sums up the contents of A1:B5 on Sheet1, Sheet2 AND a= ny sheets that are positioned in the workbook between Sheet1 and Sheet2

C.  The same cell range is used on all sheets.  References of the following=  form are not valid: =3DSUM(Sheet1A1:Sheet2!B5), =3DSUM(Sheet1A1:B5:Sheet2!=
B1:C5)


Questions
---------
1.  Based on my reading of the OpenFormula Draft, it appears that =3DSUM(['=
Sheet1'.A1:'Sheet2'.B5]) is equivalent to the Excel syntax of =3DSUM(Sheet1= :Sheet2!A1:B5).  Is that correct?

2.  The OpenFormula Draft does not explicitly say whether sheets positioned=  in the workbook between the identified sheets are included or not.  Can th= is be clarified?

3.  Are references of the following form valid?: [Sheet1.A1:B5:Sheet2.B1:C5= ]


Regards,
Eric Patterson

-----Original Message-----
From: Eike Rathke [mailto:erack@sun.com]=20
Sent: Friday, November 28, 2008 9:03 AM
To: office-formula@lists.oasis-open.org
Subject: Re: [office-formula] 3-d references

Hi Andreas,

On Wednesday, 2008-11-26 12:53:40 -0700, Andreas J. Guelzow wrote:

> I do not have any easy access to any version of MS Excel so I can't=20 
> try it out.

I just did, see below.

> [...]
> > > I think interpreting this to be ranges B4:C5 on Sheets 1 and 3 
> > > but=20 all of sheet 2 does _not_ fit into the normal understanding 
> > > of "cube"=
.
> >=20
> > Why all of sheet 2? 'Sheet1'.B4:'Sheet3'.C5 says cube from top 
> >left=20  front 'Sheet1'.B4 to bottom right back 'Sheet3'.C5, which in 
> >this=20  case encompasses 'Sheet2'.B4:C5, but nothing else of sheet 
> >2. Do we=20  have some misunderstanding here?
>=20
> That's what I understood from Erics message as of 25 Nov 2008 
>05:16:43=20  -0000 (Mon, 22:16 MST):
>=20
>=20
> > Excel, for example will sum the cells in the specified workbooks 
> > AND=20 all cells in sheets that are positioned between the 2 sheets.
>=20
> Note the "all cells" part.

I think what he meant are all cells delimited by the Z-axis edges resulting=  from the corners, cube-wise..

I tried using Excel2007 with =3DSUM(Sheet1:Sheet3!B3:C4) and no other cells=  of Sheet2 in between are evaluated. It looks like Calc and Excel have the = same understanding of a 3D reference. I would had been surprised if that wa= sn't the case.

> In his next message he reiterates that this is different from the=20
> 'Sheet1'.B4:'Sheet3'.C5 version.

That is indeed different, it doesn't work with different sheets. Or there a= re some special semantics I'm missing. IMHO that involves the ':'
range operator (as opposed to Sheet1:Sheet3!B3:C4 that probably gets treate= d as one entity, I don't know) and it looks as if the range operator doesn'= t handle 3D. It also doesn't if you define two names,
NAME1 to $Sheet1!$B$3 and NAME2 to $Sheet3!$C$4, and then use it as in
=3DSUM(NAME1:NAME2)

Calc does handle that as a 3D reference.

> It could easily be that I misunderstood but I thought it clear that 
> he=20 said there are 2 types of 3d references involved.

Maybe the range operator is the difference. Eric should be able to clarify.

  Eike