Re: Named expressions change after entering them.

[Jacob Burckhardt <bjacob startrekmail com>]

Jody Goldberg writes:
> On Tue, Dec 26, 2000 at 02:15:23PM -0800, Jacob Burckhardt wrote:
> > Package: gnumeric
> > Version: 0.61
> >
> > If I define a named expression, and later go back to view the name I
> > just defined, the expression is different than what I entered.
> >
> > Here is how to reproduce this:
> >
> > 1. start gnumeric
> > 2. press the down arrow key to move to cell a2
> > 3. choose Insert->Name->Define...
> > 4. put "n" in Name field
> > 5. put "B2" in Expression field
> > 6. click "OK"
> > 7. choose Insert->Name->Define...
> > 8. click on Sheet1!n
> >
> > Then you will see "B1" in the expression field.  But the steps above 
> > entered "B2" in that field.
>
> This was a result of parsing the expression relative to the current
> edit cell then regenerating it relative to A1.  I've patchedit in
> CVS the next release will have the change.

I think you are implying that relative to the current edit cell is a
bad thing.  But I think that is a good thing.  Having it be relative
to the current cell lets me refer to cells to the left of that current
cell.  I cannot do that if the reference point is always A1.  I will
use an example to elaborate on this and explain why this is important:

Consider a spreadsheet which says:

MPG     Gallons NumMiles 
=C2/B2  15      300      
=C3/B3  10      100      

The formula for MPG (Miles Per Gallon) is pretty easy, but if I was
calculating something else then the formula might be much more
complex.  Therefore, I might enter the wrong formula.  When I correct
it, I need to make the same correction to both of the two rows above.
I could simply copy from one row to the other but maybe the formula
appears in several places throughout the spreadsheet in many separate
non-contiguous blocks.  I don't want to have to copy the corrected
formula to so many places since that will take a long time.
Therefore, I should have started out writing such a spreadsheet by
using the Define Names feature (Control-F3).  Then make a name of MPG
and a formula of C1/B1.  Then in cells A2 and A3, I enter =MPG.  Then
if I need to change the formula, I have to change it in only one place,
namely the Define Names dialog.  That change effects all places which
use the formula.

Now suppose that I decide I would prefer a different ordering to the
columns:

Gallons NumMiles MPG
15      300      =B2/A2
10      100      =B3/A3

Again, I should not enter the formulas like above.  Instead I should
use Define Names.  But I cannot enter a formula in the Define Names
expression box which will allow the new column ordering to work.  As
you say above, Define Names are relative to cell A1.  Therefore, the name
must refer to cells to the right or below cell A1.  They cannot refer
to cells to the left since there are no cells to the left of cell A1.
The new column ordering I desire needs such a leftward reference.  So I
cannot do the new ordering with gnumeric

But Star Office can do the new ordering since its define name dialog
lets you enter a relative reference where it is relative to the
selected cell.  Therefore, if you want a leftward reference, all you
have to do is select a cell which has cells to the left of it.  For
example, you would select cell C2 and then run the define names command
and enter the expression which I show in the new ordering spreadsheet
above.  Specifically you enter =B2/A2.  I can't easily run Excel, but
back when I used to run Excel more often I think I remember it working
like Star Office.  I recommend that gnumeric also work this way.

> Thanks
>     Jody