Re: [office-formula] What should we do if you reference outside asupported range?

[Eike Rathke <erack@sun.com>]

Hi David,

On Monday, 2008-06-09 23:34:37 -0400, David A. Wheeler wrote:

> > If an application does not support some column (say "ZZZ"),
> > and a formula tries to reference it (e.g., [.ZZZ1]), what should happen?

Generate an error. OOo currently generates #NAME? for this in both, UI
and file format, which might be changed in future to #REF! instead for
the file format.

> There's an minor implementation issue for spreadsheet import.
> If it's a (human) input error, you could presumably not even accept it.
> But if you're reading in a file, you'd like to be able to read in as much as
> you can, even if you can't handle it all.
> 
> Let's say a document refers to [.ZZZZZ1], and your app
> doesn't support that many columns.  The apps that I'm aware of
> store columns as an unsigned value with a fixed number of bits,
> and thus couldn't even STORE that reference in their data format
> for references.

Except all formats that store in XML for example, e.g. MOOXML and ODF.
It is already sufficient that an application capable of handling
2 million rows or so stores in ODF and another application with lower
limits reads that file in. =A2000000 or =SUM(A1:A2000000) should
definitely display an error then.

> I believe most apps currently just translate such references to
> a constant error value, and leave it at that.  Which means that
> it's a constant error.

There's nothing wrong with that. Unless the referred cell range was
empty, if such a document was resaved it would be corrupted anyway.

> Where possible, translating it to OFFSET(...) is a better idea (I think);

"Translating" expressions to something else usually is not desired.

> that would cause the least amount of data loss.

If it is about preserving the original range expression, translating it
to an "error constant with string representation" using the original
range string would do without modifying the expression, which in fact is
what OOo does. However, this is implementation defined. Still doesn't
help though if the document was resaved and then reloaded with larger
limits again because the original out-of-bounds data is lost.

  Eike

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

PGP signature