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

Steve.

Good question, this behavior will catch the unwary because they are
using relative reference when they want to use absolute referencing.
Often users forget they can use to absolute referencing to control the
location the formula uses.
On Thu, 2011-08-25 at 09:55 +1200, Steve Edmonds wrote: 

> Hi.
> Noticed an unexpected behavior in calc in regards cell references 
> changing when data is cut and pasted. Not what I expected but others may 
> consider it normal. Goes from LO3.4.1 back to OOO3.2.1

I am using 3.4.2

> In a new sheet, column A, from A1 enter p,f,p,f,p,f.
> In column B1 enter formula =if(A1="p","pass","fail") and copy down.
>
> If there is a "p" in A there is a "pass" in B as expected.

Same

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

Same

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

The cell reference in B is automatically updated to the position where
the data is pasted, this is because you are using relative references,
the cell reference is updated to the new location of the data 

> Highlight A1:A6, copy and paste down 1 row. The "p" and "pass" still 
> line up. An expected result.

Copying overwrites the original cell contents. The cell reference is unchanged. This is the same as 
putting a new value in the cell.

> What should be the result of a cut then subsequent paste. The above 
> seems prone to cause problems without a warning that cut and paste will 
> modify all formulae referencing cut cells.

Any cutting and pasting, whether part or full column, will cause the
references in B to automatically update to the locations of the pasted
data when using relative referencing 

> If I am analysing some entered data and think to myself "I will just cut 
> and past this data over there while I type in some new numbers" then I 
> have just unknowingly wrecked my analysis.

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



-- 
Jay Lozier
jslozier@gmail.com

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