GNOME Bugzilla – Bug 312120
Named range formulas are defunct after deleting a row
Last modified: 2007-11-21 03:18:30 UTC
Please describe the problem: Named ranges are a covenient way to define spreadsheet data. The range name can then be used as part of a formula. In Gnumeric, deleting a row or column from a named range can render all the formulas based on the range defunct. The #REF! error replaces all results based on the named range. Excel handles this without a problem. The range is redefined to account for the deleted cells, and formulas that depend on the remaining data continue to work. Steps to reproduce: 1. Create two columns of numbers, and name them "range1" and "range2". 2. Create a third column with the formula "range1+range2" in every cell. 3. Delete any row that transects the three columns. Actual results: Cells in the third column (with the formula) show the sum of the range1 and range2 cells in the same row. Deleting any row that contains cells from the named ranges causes all the cells in column three to display "#REF!". Expected results: The named ranges should continue to be defined even if some of the cells within them are deleted. Any formulas that depend on named data should continue to work, as long as the actual data isn't deleted. Does this happen every time? Yes. Other information: Nope.
See also bug 123649.
I've managed to replicate this bug in svn trunk, but not consistently. Most times deleting the cells works as expected. Other times you get the #REF! error. When the error occurs what seems to have happened is that the range has been redefined incorrectly; for example if range1 was $A$1:$A$6 and row 4 is deleted it sometimes gets set to $A$1:$A$4 instead of $A$1:$A$5. This gives the #REF! error in any cell that is referencing the $A$5 bit of the range is it is actually outside the range now. This may be related to bug 498347; if you get the #REF! error and undo and redo the cell deletion then gnumeric will crash.
So you have a named expression "FOO" with value "$A$1:$A$6" that sometimes gets handled incorrectly ?
Believed fixed together with the problem in bug 498347.