Re: [office-formula] 3-d references

[Eike Rathke <erack@sun.com>]

Hi Andreas,

On Wednesday, 2008-11-26 10:39:58 -0700, Andreas J. Guelzow wrote:

> > > Given the same reference below (=SUM(['Sheet1'.B4:'Sheet2'.C5])),
> > > [...]
> > > SUM(['Sheet1':'Sheet2'.B4:C5]
> > 
> > Yes, that's equivalent. Though it would be SUM('Sheet1':'Sheet2'!B4:C5)
> > in Excel.
> 
> I believe in Excel 'Sheet1'.B4:'Sheet2'.C5 refers to many more cells
> than 'Sheet1':'Sheet2'.B4:C5 if there is a sheet between Sheet1 ad
> Sheet2.

Do you mean that in Excel those two have different semantics, and the
first does include all sheets in between where the second does not?
I don't think so, just tried in Excel2007, the first (with ! instead of .)
gave a #VALUE! error, only the second works and evaluates sheets
between.

The expression SUM('Sheet1'!A1:'Sheet1':B1) is accepted and evaluates
correctly only for identical sheets, it seems. Doug or Eric, could you
please confirm or deny?

> So they are not equivalent!

Also Calc includes all sheets between Sheet1 and Sheet2 in a reference
'Sheet1'.B4:'Sheet2'.C5. I think this is expressed by "the cube"
mentioned in my earlier mail.

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.

PGP signature