To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=68202
Issue #|68202
Summary|Deleting a Column or Row within a Range
Component|Spreadsheet
Version|OOo 2.0.3
Platform|All
URL|
OS/Version|All
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|FEATURE
Priority|P3
Subcomponent|code
Assigned to|spreadsheet
Reported by|discoleo
------- Additional comments from [EMAIL PROTECTED] Sun Aug 6 13:50:59 -0700
2006 -------
ADVANCED CONTROLE over how ranges change when a formula containing such a range
is moved/ copied/ changed (through insertion/deletion within the range).
The automatic adjustment of a range whithin a formula when the cell is copied is
both at times very useful and a pain in the ass at other times.
I will briefly describe some limitations, and then will offer a more advanced
workaround.
LIMITATIONS
===========
Lets say we have the range A1:D10. We can have this range written as:
- relative addresses: A1:D10 (chnages almost always, BUT NOT at a very special
time, see later...)
- absolute: $A$1:$D$10 and the various combinatins (does NOT change sometimes
when it is HIGHLY DESIRABLE TO CHANGE, see later)
- we can name this range, e.g. RangeAD (the same as before, see later...)
What is the great limitation. It happens from time to time that we insert or
delet a row or a column and everything shifts correspondinlgy. The problem is,
the range does NOT addapt to this change (when the range itself changes and is
not merely moved). I do NOT mean here, the changes when I insert/delete data
OUTSIDE this range, when adjustments are performed adequately.
SHOULD THE RANGE ADAPT AUTOMATICALLY?
The answer is definitely NO! Sometimes we will enter new data, so that the range
should remain unchanged. However, at other times, we wish the range to change.
Obviously, we need another identifier to specify when it should change and how
it should change.
SOLUTION
========
Here is an elegant solution to this problem. We should implement a special
function, lets call it 'reference()', which accepts a cell/ cell range as input,
and also various conditions, i.e.:
ARGUMENTS:
- cell OR cell range
- options:
- what should happen when copying the cell containing the formula
- on_copy_horizontal: update / do NOT update (similar to $A1:$D10)
- on_copy_vertical: update / do NOT update (similar to A$1:D$10)
- changes when moving the cell (as above)
- changes when dragging the right lower corner (see above)
- changes on deleting a row/column: (basically a cell shift will happen)
- on_shift_up: shrink range by 1..x rows / do NOT shrink
- on_shift_left: shrink range by 1..y columns / do NOT shrink
- changes when inserting a row/column (this is basically a shift to the
right/down)
- on_shift_right: expand range by 1..x columns / do NOT expand
- on_shift_down: expand range by 1..y rows / do NOT expand
NOTES
=====
The absolute reference ($) could be used as a shortcut for the on_copy
change/options.
The really new thing is the 'on_shift_...' property, which describes what
happens when a row/column within the range is inserted/deleted.
IMPLEMENTATION SUGESTION
========================
Because it could be unwise or time-consuming to re-evaluate on a low level every
cell in the sheet when something like this happens, OOo Calc could automatically
save this range (when it encounters the function reference()) in a special part
of the sheet (a header as described in issue
http://qa.openoffice.org/issues/show_bug.cgi?id=67499 ). It then could create an
invisible link (hard link) to this value. In this way, if this same range is
encountered more than once (using the same options), it would be modified once
but the changes would be present automatically in every place. So OOo would have
to scan only this area for changes to perform.
EXAMPLE
=======
Lets say we have the range A1:A10, and we wish to calculate the mean of these
values. We write in cell A11: '=AVERAGE(A1:A10)'. (We could have defined a name,
too, for this range.)
However, we later discover that we have to delete one row (e.g. row 5) in this
range, therefore the new range will be A1:A9 and the formula will be in the cell
A10. However, the formula points to the range A1:A10, therefore an error is
generated. This is true for names, too.
Even if NO error occurs (if the change does not imply a cyclic evaluation), the
results would be still wrong, because the new range is different from that in
the formula.
With the proposed solution, we could have written:
'=AVERAGE(REFERENCE(A1:A10, on_shift_up = 1, on_shift_down = 1))'
This would be especiall valuable when defining names. As described in the issue
on headers, we could define the name using this function, so that it will
consistently change, if any row/column is inserted/deleted.
As an example:
= in the header =
// this are the blood-ressure measurements (some comment)
RangeAD = REFERENCE($A$1:$A$10,on_shift_up = 1, on_shift_down = 1)
The 'RangeAD' would be adjusted automatically when we decide later to delete
rows/columns or add some rows/columns. (We could have a special option to adjust
automatically when inserting immediately after last row/column, OR consider the
'space' immediately beyond the last row/column as 'belonging' to the range,
too.)
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
