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 jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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