# Calc sort messing up formulas? [closed]

I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (**ALL** outside of the sorted block, but referring to things *inside* the block) stayed the same, as expected.

I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is **VERY** annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.

I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.

Which LibO version? Which OS? Did you have a look at: http://ask.libreoffice.org/en/questio... ?

Sorting messes them up in Google Sheets too.

The problem still exists in Version: 4.3.0.4 Build ID: 62ad5818884a2fc2e5780dd45466868d41009ec0, but was introduced at least one or two versions prior.

I just looked at the link from ROSt52 and it appears to be the exact same issue I have. It, too, has no answers. I was going to try to create an example, but I could not figure out how to attach it to my question.

I just looked a little closer and discovered that it works fine sorting two rows that BOTH have values in them. When I included a third row that was blank, the formulas still

correct, but the results areLOOKWRONG. For example, my sheet currently shows that 1695.73-863.78+0 is 1695.73. Pressing F9 does NOT fix it. However, replicating the [partial] row of formulas just above the sorted block,throughthe block fixes it. As far as I can tell, the formula text shown didn't changeYou should now be able to upload.

Thanks, I found the 'add attachment' option if I edit my original question. However...

I'm having trouble generating a simple example. I found a block on my checkbook sheet where the problem occurs reliably (i.e. I change the sort key and sort again and the formulas still don't update, or update incorrectly) and copied it into a new sheet. In the new sheet, the sort always works perfectly.

Sigh. It would seem that for 2015, I must abandon my 22 yr old workbook (1 sheet/yr) and start over.