Re: [office] Data Grid Size element proposal

[Eike Rathke <erack@sun.com>]

Hi Warren,

On Friday, 2008-11-21 18:09:59 -0800, Warren Turkal wrote:

> I don't really see the problem with an error being caused by a
> reference to a cell outside the user agent's range going away when
> opening the doc in a user agent with a greater number of cells.
> [...]
> I could only see that mattering if you rely on the error for some
> logic in your spreadsheet. That just seems like a bad idea.

People do have the strangest ideas when "designing" spreadsheets..

Andreas already gave examples where formulas would evaluate to
a non-error in a larger grid size. Let me add another not so obvious
example not involving errors at all, which is with the defined named
ranges I already mentioned that wrap around the sheet's edges when used
with different offsets, which users may or may not be aware of,
I suspect most aren't but some may use it as an obscure feature.
[The entire idea of this wrap-around IMHO was bad from the beginning,
but ...]

- In an application that supports 256 columns maximum, e.g. Excel2003 or
  Calc2.4, enter the following values in cells:

   A1: 1
   B1: 2
  IU1: 4
  IV1: 8

- Position the cell cursor on C2.
- Open the Define Names dialog (menu Insert->Name->Define or such).
  - Define a name MYNAME and as the reference define A1:B1, note the
    relative addressing without preceding $ characters.
- In cell C2 enter the formula =SUM(MYNAME), result is 3.
- In cell A2 enter the formula =SUM(MYNAME), result is 12.
  - That is the wrap actually results in the range IU1:IV1, you may
    easily see if you invoke the Define Name dialog again on cell A2 and
    look at the definition of MYNAME.

Now if this file was loaded in an application that supported more
columns without knowing the original maximum grid size, the formula in
A2 would yield 0 because the range wrapped pointing to then empty cells.
What applications actually can or should do about this is beyond the
scope of the file format standard, nevertheless it seems to be a good
idea to include the hint of the original grid size.

  Eike

-- 
 OpenOffice.org / StarOffice Calc core developer and i18n transpositionizer.
 SunSign   0x87F8D412 : 2F58 5236 DB02 F335 8304  7D6C 65C9 F9B5 87F8 D412
 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS

PGP signature