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 LOOK correct, but the results are WRONG. 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, through the block fixes it. As far as I can tell, the formula text shown didn't change
You 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.