To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=74625
Issue #|74625
Summary|Calc: Transposition: Formula Error
Component|Spreadsheet
Version|OOo 2.1
Platform|All
URL|
OS/Version|All
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|DEFECT
Priority|P3
Subcomponent|code
Assigned to|spreadsheet
Reported by|discoleo
------- Additional comments from [EMAIL PROTECTED] Sat Feb 17 15:54:22 +0000
2007 -------
This deals with an ERROR in the *automatic Formula Update* when transposing a
selection in Calc.
1. ERROR in Referenced Cells: wrong formula update
2. VLOOKUP and HLOOKUP are NOT updated
1. Wrong Formula Update:
====================
This happens when pasting a transposed selection containing formulas over the
same location.
a. STEPS TO REPRODUCE:
==================
1. create a spreadsheet:
A2: = 1 ; B2: = B2 * 2
A3: = 2 ; B3: = B3 * 2
...
A6: = 5 ; B6: = B6 * 2
2. Select A2:B6
3. CUT the selection
NOTE: the selection is cut away,
so NOTHING is displayed in A2:B6,
giving the false impression that
PASTing here is unproblematic
4. CLICK on A2
5. PASTE special; check the 'TRANSPOSE' checkbox
6. new data is in A2:E3
7. the cell A3 displays an ERROR: '#REF!'
The content in this cell is "=#REF!2*2". It should have been "=A2 * 2". All
other formulas in the other cells are updated correctly.
I do NOT know how exactly the formula update mechanism operates, BUT this should
be pretty simple and could be viewed as an affine transform.
b. CORRECT FORMULA UPDATE
======================
Lets say the initial data is in [CbRb; CeRe], where C stands for "Column" and R
for "Row". b = begin (first element); e = end (last element);
Transposing this selection and pasting it to C'[0]R'[0] will yield this:
the new data matrix will be:
" C'[0]R'[0]" - first cell
"(C'[0]+Ce-Cb)(R'[0]+Re-Rb)" - last cell,
where C'[0] is the number of the (first) column and
R'[0] is the number of the (first) row where the data is pasted;
For an arbitrary initial cell (Ci and Rj), the new values will be:
C'[i] = C'[0] + R[j] - Rb
R'[j] = R'[0] + C[i] - Cb
c. SPECIAL CONDITIONS
==================
IF one cell contains a formula referencing a selection/ matrix that OVERLAPS the
transposed selection, BUT extends beyond its boundaries, then an ERROR should be
generated. The transposition would split the initial matrix into a number of
NON-contiguous matrices and the user probably did NOT want this.
In pseudocode, this would look like:
[CbRb, CeRe] - initial matrix before transposition
[CiRi, CjRj] - referenced matrix inside the formula
code to update formula and detect error:
IF((Ci <= Ce) && (Cj >= Cb) && (Ri <= Re) && (Rj >= Rb)) {
// THIS MATRIX CONTAINS ELEMENTS FROM THE MATRIX
// THAT WILL BE TRANSPOSED, SO IT MUST BE UPDATED
IF((Ci < Cb) || (Cj > Ce) || (Ri < Rb) || (Rj > Re)) {
// THE MATRIX EXTENDS BEYOND BOUNDARIES
// OF TRANSPOSED MATRIX => ERROR
// THE INITIAL MATRIX WOULD BE SPLIT INTO
// AT LEAST 2 NON-CONTIGUOUS MATRICES
SET_ERROR("MATRIX OVERLAP");
}
ELSE {
// UPDATE FORMULA
C'i = C'[0] + Ri - Rb // FIRST ELEMENT
R'i = R'[0] + Ci - Cb
C'j = C'[0] + Rj - Rb // LAST ELEMENT
R'j = R'[0] + Cj - Cb
_SPECIAL_UPDATE(); // SEE POINT 2: LOOKUP
}
}
2. UPDATING LOOKUP
===============
When transposing a matrix, any LOOKUP function that references that matrix
should be updated.
VLOOKUP() should become HLOOKUP(), and
HLOOKUP() should become VLOOKUP()
[only IF 2nd argument references the transposed matrix]
In pseudocode, this would look like:
function _SPECIAL_UPDATE() {
IF(_IS_ARGUMENT_OF_FUNCTION) {
IF(_IS_FUNCTION(VLOOKUP) && _IS_2nd_ARG) {
function = HLOOKUP;
}
ELSE IF(_IS_FUNCTION(HLOOKUP) && _IS_2nd_ARG) {
function = VLOOKUP;
}
}
}
---------------------------------------------------------------------
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]
