*From*: Jacob Burckhardt <bjacob startrekmail com>*To*: Jody Goldberg <jgoldberg home com>*Cc*: gnumeric-list gnome org*Subject*: Re: Named expressions change after entering them.*Date*: Fri, 19 Oct 2001 22:26:52 -0700

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

**Follow-Ups**:**Re: Named expressions change after entering them.***From:*Jody Goldberg

[Date Prev][Date Next] [Thread Prev][Thread Next] [Thread Index] [Date Index] [Author Index]