[libreoffice-users] Re: Calc cell reference unexpected behavior.

Am 25.08.2011 02:56, planas wrote:
> You try using absolute references =if($A$1="p",...) to make sure the
> formula always uses the data in the specific cell. When you cut and
> paste the data, the formula always reference to the same cell and does
> not follow the data. Note you can use partial absolute referencing
> ($A1/A$1)  in some situations.
>
>
>
> > steve

Steve,
This has nothing to do with relative vs absolute referring.

1.1: When moving referenced cells, all references will follow, no matter 
if you cut&paste or drag&drop, regardless of absolute or relative 
references. You move the same data to another location and all 
references will follow the same data (bug: except conditional formatting).
1.2: When copying the referenced cells, this has no effect on the 
references. They still refer to the same (original) data in the original 
place.
2.1: When moving referring cells, all references will refer to the same 
location as they used to, no matter if you cut&paste or drag&drop, 
regardless of absolute or relative references.
2.2: When copying referring cells, this also will not change the 
relative nor absolute references because relative
Sheet1.A1 =Sheet2.B2
will still refer to the next row, next column, next sheet when copied to
Sheet3.Z99 =Sheet4.AA100
Nothing really changes *because* the relative addressing adjusts its 
appearance in order to do exactly the same as before.
OT: This is the reason why I prefer the Excel R1C1 notation. IMHO, this 
notation is the greatest improvement in LibO Calc. You copy a relative 
reference and it looks exactly the same. =R[1]C[1] means "row +1, column 
+1. Unfortunately, the sheet component does not follow this convention. 
It is not even relative as it uses to be in the original notation.

> Highlight A1:A6 and drag down 1 row. The "p" and "pass" no longer line up. An expected result as 
> the cells are effectively moved.
> Highlight A1:A6, cut and paste down 1 row. The "p" and "pass" no longer line up. An unexpected 
> result. I am cutting the data to remove it and pasting the data to enter it.
In both cases you follow the above scenario 1.1. You move referenced 
cells and the referring cells keep track of them.

> Highlight A1:A6, copy and paste down 1 row. The "p" and "pass" still line up. An expected result.
You modify the referenced cells and the referring cells do not change by 
any means.

> What should be the result of a cut then subsequent paste.
The referring cells keep track of the original data, each subsequent 
paste is a copy.

Hope this helps,
Andreas


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted