GNOME Bugzilla – Bug 123649
Defined name does not update when cell moves
Last modified: 2008-01-30 15:00:43 UTC
If you give a cell a name, with insert-define-name, then cut and paste the cell to a different location, the defined name does not update with the new cell cordinates. Also if you insert a column or row, the name does not update with the new location.
This is known. I've even roughed in the beginings of a solution. However, I got bogged when I realized that relative references in names should be handled differently than in standard expressions. It boils down to research. Someone has to sit down and analyze how (and if) a relative reference in a name should change eg $A$1 : should relocate A$1 : ?? $A1 : ?? A1 : no My guess is no. we should confirm
I use the names to define cells that are used as inputs to formulas or as resultants of a formula. So in my case I would expect the cell to move relitive so that furmulas would not break. For example I might have cell B2 named LengthA and Cell C2 named LengthB. Then in cell D2 I might have SQRT(POWER(LengthA,2)+POWER(LengthB,2)) and name this LengthC. Now if I decide that I want to add a row above row 2, or cut and paste all this some where else in the worksheet, then all my formulas will break. :-( I would guess that most people use names to refer to a specific place in the worksheet, and would want it to move around. Just my 2 cents worth. Thanks
I agree with Daniel. I would love it if the assigned cell names would move with the cells.
*** Bug 304920 has been marked as a duplicate of this bug. ***
Some observations... When you bring up the Excel name dialog, expressions are displayed and parsed relative to the cell from which you called up the dialog: 1. Select A1. 2. Define FOO=sheet1!a2 [no $$] 3. Select A10 4. Bring up the name dialog and see FOO=sheet1!A11. 5. Select A65536. 6. Bring up the name dialog and see FOO=sheet1!a1. [wrap-around!] Double-$ references clearly identify sheet areas and move as expected when columns are inserted or deleted. Specifically when a column containing a name is deleted, its definition changes to =sheet1#REF! (and not just #REF! for some reason). No-$ references seem to ignore insertion and deletion of columns. Single-$ references seem to ignore for the non-$ and respect for the $ coordinate. Altogether pretty reasonable by Excel standards.
More notes... * Deleting a range does not affect names. * Shifting cells does affect names. * A name that gets shifted off the sheet becomes #REF!
5/25/05 Please note that my experience with GNUMERIC is different than Morten Welinder's reply pasted below. Namely, with GNUMERIC 1.4.2 running under Windows XP, Version 2002, service pack 1, the named-ranges do not update correctly when rows are inserted (or deleted) in a worksheet above a named range. For instance: (1) Define the cells C6 and C7 as range "dummy". (2) Put values 3 and 4 into cells C6 and C7 respectively. (3) Input formula "=sum(dummy)" into cell E9. (4) Result in cell E9 is "7" as expected. (5) Go to row 4 by selecting cell D4. (6) Insert a row by using pull down menu Insert/Rows (7) Display in cell E9 changes to "#REF!" (8) Formula in cell E9 has changed to "=sum(#REF!)" Following same proceedure except deleting a row instead of inserting a row above the named range has identical effect-- i.e. reference to named range changes to #REF! in formula in cell E9. Note that neither the cells in the named range or the cell with the formula were shifted off the worksheet. This doesn't square at all with Morten Welinder's comments. Thanks for looking into this. Again, being able to use named ranges is very important. If I can help in any other way let me know. (Attached a workbook perhaps?) Toby Wehrhan twehrhan@yahoo.com 530.756.6986 -- Reply to my bug report here------ Please DO NOT reply to this by email. All additional comments should be made in the comments box of this bug report. http://bugzilla.gnome.org/show_bug.cgi?id=123649 Gnumeric | General | Ver: CVS ------- Additional Comments From Morten Welinder 2005-05-25 14:15 ------- More notes... * Deleting a range does not affect names. * Shifting cells does affect names. * A name that gets shifted off the sheet becomes #REF!
twehrhan@yahoo.com: the main thing you missed was that I was not describing Gnumeric's current behaviour, but rather Excel's. The is no disagreement that Gnumeric's behaviour is not desireable, but we need to figure out what the right thing to do is.
Bringing this to the front of the queue. We need this resolved before 1.8.0
This problem has been fixed in the development version. The fix will be available in the next major software release. Thank you for your bug report. This could probably need some testing.
*** Bug 513053 has been marked as a duplicate of this bug. ***