How to prevent cross-references from changing when shifting cells?

[Village]

I have used LibreOffice Calc for fifteen years, previously OOo, but now I observe a strange change in behavior.

If I set A1 to “=B1”, it shows the value of B1.

If I then drag B1 to another location, the code inside A1 suddenly changes to point to B1’s new location, so instead of showing the blank value, or 0, it is showing F3 or where ever I moved B1 to.

This change in behavior is extremely frustrating, as I have many large spreadsheets, and its filling the data with errors everywhere. Now, every time I shift a single cell or worse, a whole group of content, I risk some other cross-reference somewhere else in the Calc file being pointed to the wrong place, and it is a ton of work to correct each time. I have dozens of sheets that have to be entirely redone due to this strange behavior!

How can I disable what appears to be a new behavior for Calc?
LibreOffice, Version: 5.2.7.2

[mikekaganski]

Answering specifically “what appears to be a new behavior for Calc” with this screencast from OOo 1.0.3.

[gtomorrow]

@mikekaganski you’re one of the most informed people on this site, being an actual LO developer and long time contributor. With all due respect, the above is not an answer, regardless of what @Village describes as new or not, and I’m kind of surprised you would respond as such instead of commenting. Regardless, please, is there a way to prevent this from happening or not? I tried to make the links absolute (e.g., $B$1) but the same thing happens.

[mikekaganski]

Well - I am not aware of such a setting, that’s why I didn’t answer that part, in the hope that others who know ( @erAck maybe?) would complement. Still, I suppose the above to be an answer of a kind, because since the problem didn’t happen before, it might be because of some other workflow change of OP… but no problem, if you think it’s better posted as a comment, I’ll re-post.

[Village]

Yes, perhaps I never noticed this behavior before, but recently I’m doing a big project that requires a ton of shifting of the data downwards, so maybe I just am using LibreOffice differently. Is there a way to stop this behavior?

[erAck]

That is not a new behaviour, it has always been like that, and it is expected and implemented by most (if not all) major spreadsheet applications. If you drag&drop or cut&paste cells that are individually referenced by other cells, those references are adjusted to point to the new location.

If you want location “persistent” references then use a named range/expression with a relative reference, e.g. for Mike’s screencast example on cell Sheet1.A1 define a named range thatcell to $Sheet1.A1 (note it’s not $Sheet1.$A$1) and in Sheet2.A1 use the formula =thatcell. As both column and row reference particles are relative you can use the same =thatcell formula expression in Sheet.A2 to obtain the the value of Sheet1.A2 and so on. The underlying relative reference of such named expression is not adjusted if cells are moved.