Calc Cut / Copy / Paste error in cells containing IF(...)

English
#1

Calc does not correctly update the Row numbers in an IF(…) when I Copy / Paste an entire Row.
Copy / Paste works correctly when I Copy / Paste a single cell.
The cell contents that is not being handled correctly contains:
=IF(J65<3,J65+1,1)

The J65 entries do not get updated when a Row which contains a cell with this IF() is Copied / Pasted.

I believe this is a bug.
Does anyone know of a work-around, or whether this is on a list to be corrected ?
It’s been this way for a LONG time. Open Office and Google Sheets exhibit the same problem.
Thanks

#2

Don’t know what you’re talking about, entering such formula in a cell and copy-pasting the entire row to another row adjusts the row part as expected. Please upload a sample .ods file (edit your question using three dots and then pencil, and attach file with the upload icon) and state what exactly you are doing step-by-step, what you expect, and what you are seeing instead.

#3

Thanks for your reply.
After doing some experimenting to answer you, I think I understand what I was running into.
The contents of the IF() refers to the previous row in the sheet.
To add a new row above an existing row, I selected an existing row and used the Insert Above command to add a row above.
Doing that results in the row numbers in the IF() not being updated, so the IF() no longer refers to the row above the IF(). I guess there’s some logic to that but it’s not the behavior I expected, and I don’t know if it would be considered a bug. Maybe not.

Selecting the row ABOVE the IF(), then using the Insert Above command results in the row numbers in the IF() being updated as expected, leaving that IF() referring to the row above it.

I now know how to get the behavior I want, so I’m set.
Thanks again for your reply.
Jeff

Licensing Sheet Example for Forum.ods (22.9 KB)

#4

It’s the expected behaviour that if you insert/delete columns/rows or move cells around the cell references still point to the same cells as they did before. Every major spreadsheet implementation does it. To have an “always reference the row above even when moved” behaviour use a named expression with a relative reference, like on B7 hit Ctrl+F3 and define a name Above with the expression B6 and as cell formula use
=IF(Above<3;Above+1;1)

Maybe you would like to tell others what you are doing to get the behaviour you want?

1 Like
#5

The way I got the behavior I wanted is described above.
Thanks for the pointer about defining ABOVE. I’ll look into that.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.