Using Libre Office, Linux Mint 19.2, Flatpak release.

I have a simple function that needs to have a static row: =K15-G$2. Not sure when the error started occuring, but after I inserted (3) rows above row #2, the function changed to =K15-G$5. If I copy/move this function, the original values remain as expected. I tried modifying it to read =K15-$G$2, but that made no difference.

In this case, how can I get absolute cell references to work?

  • 1
    =K15-$G$2 should have worked, according to the documentation; are you sure it didn't do the right thing? – Jeff Schaller Aug 14 at 15:03
  • (assuming G2 is the absolute cell you're trying to get to, that is) – Jeff Schaller Aug 14 at 15:04
  • Positive - that's what's so weird, especially with this being such a simple formula. – ajgringo619 Aug 14 at 15:04

localc is working as documented (and as it should, and as every spreadsheet has done since the days of Visicalc).

It's a feature, not a bug.

Your formula referred to cell G$2. You inserted rows which moved G$2 to G$5 so localc updated all references to the old G$2 to its new location of G$5, preserving the reference to the same data.

In short: the cell was moved so ALL references to it had to be updated to match its new location.

This needs to happen whether the reference was relative or absolute - the important thing is the data the reference was pointing to.

If it didn't do this, then every time a column or row was inserted or deleted, every formula referring to any cell that got moved as a result of that operation would be broken because it would be pointing to the wrong data.

The same thing happens when you cut and paste a cell from one location to another - if any formulae refer to it, they are updated to refer to the new location. This allows you to move a cell or multiple cells to a different location on a sheet or even to a completely new sheet.

BTW, if you had inserted rows below G$2 then G$2 would not have been moved and there would be no need to update any references to it. This happens frequently when you need to insert a row or rows between two existing rows in a table. Or when you sort the data in a table.

  • OK, this makes sense. – ajgringo619 Aug 15 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.