Re: Debian bug report for gnumeric

[Jody Goldberg <jody gnome org>]

On Sat, Feb 15, 2003 at 09:18:38PM +0000, Faheem Mitha wrote:
> I just want to draw the developers attention to
>
> http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=172307
>
> I have no idea whether this report was sent upstream or not, but it
> has been open for a while now.
>
> If it has been fixed in recent gnumeric versions, let me know and I
> will close the bug report.
>
> Please cc me on any reply. I'm not subscribed to this mailing
> list. Thanks.

This is not a bug, but has come up often enough that I've researched
the magic necessary to do what you expect in this instance.

The trouble is that you are sorting relative references.  When the
underlying expressions move the value of the expression changes (in
this case back to the original value).  For a while in 1.0.x I tried
to support this use case directly, but it broke things in more
profound ways when sorting  expressions that mix absolute and
relative references.

After some experimentation it seems that excel's solution is to do
what you are expecting with relative references with no sheet
qualification, and to do the correct thing when there is a sheet
qualifier.

eg

    A1  : =B1
    A2  : =B2
    A3  : =B3
    A4  : =B4
will treat the relative references as absolute when sorting (the
behavior you're expecting).  Whereas if you enter in _either_ Sheet1
or Sheet2

    A1  : =Sheet1!B1
    A2  : =Sheet1!B2
    A3  : =Sheet1!B3
    A4  : =Sheet1!B4

it will treat them as relative references and adjust them.  I've not
had time to implement this for the time being, but it might make it
into 1.2.0.

The code in question is well localized, but logically dense.  It
does not require a huge amount of gnumeric knowledge, but
implementing it would take some spreadsheet savy and comfort with
boolean logic.  Anyone out there think they're up to the challenge
:-)

The core logic is in
http://cvs.gnome.org/bonsai/cvsblame.cgi?file=gnumeric%2Fsrc/expr.c&rev=&root=/cvs/gnome
    cellref_relocate 
    cellrange_relocate