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

["David A. Wheeler" <dwheeler@dwheeler.com>]

> If an application does not support some column (say "ZZZ"),
> and a formula tries to reference it (e.g., [.ZZZ1]), what should happen?
>
> The "obvious" answer "that is an Error".
> Should we specifically say that?
> Should we permit an alternative
> (e.g., presuming that they are empty cells)?

> I could only see this happening from either user input error, or from
> importing a spreadsheet created in an app that supported larger ranges
> than your spreadsheet.  In both cases signally an error would be the safe
> thing to do, since it warns the user that something odd happened....

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.  Thus, if it's to be an error, it would need to be
transformed (on input) to some other representation that
produces the error... and preferably one with the least data loss.

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.

Where possible, translating it to OFFSET(...) is a better idea (I think);
that would cause the least amount of data loss.  I think that'd be
worth recommending in the spec itself.
I should check and make sure that an OFFSET to an
out-of-range cell is guaranteed to be an Error by the spec,
but clearly that would make sense. You could even
round-trip the in-bound portions, though the loss of the
out-of-bounds data makes that less impressive :-).

--- David A. Wheeler