Issue 68202 - Deleting a Column or Row within a Range
Summary: Deleting a Column or Row within a Range
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.3
Hardware: All All
: P3 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2006-08-06 21:50 UTC by discoleo
Modified: 2013-08-07 15:12 UTC (History)
2 users (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description discoleo 2006-08-06 21:50:59 UTC
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

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.

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.

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.:
 - 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
      - on_shift_right: expand range by 1..x columns / do NOT expand
      - on_shift_down: expand range by 1..y rows / do NOT expand

The absolute reference ($) could be used as a shortcut for the on_copy

The really new thing is the 'on_shift_...' property, which describes what
happens when a row/column within the range is inserted/deleted.

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 ). 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.

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.)
Comment 1 discoleo 2006-09-01 14:29:42 UTC
This is a short explanatory note: (as this was probably not as easy to understand)

The most important thing described in this issue, is an easy way to control what
happens, WHEN a cell-range (column or row), WITHIN THE ORIGINAL RANGE is deleted.

As an example:

we may have the range A1:A500

What should happen to this range IF we deleted rows 101-200:
 - should the range change to A1:A400
 - or should it be left unchanged (A1:A500)

The $A$1:$A$500 does NOT specify what should happen (and there is NO default
action for this, as in different settings we may wish different handlings).

Similarly, we will need an additional sign beyond '$' to adress this issue,
therefore the sugestion for a function ('REFERENCE()'), that would accept
various parameters to control what happens when:
 - a column/row WITHIN the range is deleted
 - the range (formula) is moved, copied as a whole
Comment 2 frank 2006-09-13 12:47:05 UTC
requirements have to decide