I recently migrated from Microsoft Office to LibreOffice. Sure there are some differences and there was some learning to do, but overall everything has gone pretty smoothly. With one major exception. Calc.
I use Calc for a lot of everyday things. Among other things, simple accounting. And this is where I ran into problems.
My simple accounting
Each year has its own calculation sheet. Each such sheet is divided into twelve tabs, one tab for each month.
On each tab (month) there are several different accounts. Each account retrieves data from the previous tab’s (month’s) last value (i.e. from the specific cell where the value is found). The formula used for this is the simplest of them all: [=SUM()]. In the same way, each new year’s calculation sheet retrieves its values from the previous year’s last values for the respective account.
When I work in a sheet for a specific year, and things go wrong… :
As long as I don’t make any major changes, everything works as it should.
However, problems can arise when rows are deleted (**Ctrl, -**) in a tab. The tab that follows that tab doesn’t seem to be able to handle that change. That is, the calculation in the immediately following tab retrieves the value from the place that was correct before the rows in the previous tab were deleted.
You discover it quite quickly. It’s easy to fix. But quite annoying (in Excel I never had that problem).
What is worse, however, is what happens when you open spreadsheets from previous years. These spreadsheets were perfectly functional and correct the last time they were used. Everything was as it should be. Each sheet calculated the correct values and the subsequent years retrieved and left their values as they should, up to the last calculation made in the last year’s calculation sheet.
However, for some strange reason, something happens (strange miscalculations) when you reopen the spreadsheets from previous years, which becomes clear when the spreadsheet for the current year is opened. You notice it right away because you know what is correct and especially if you have other, external, sources for certain accounts, such as accounts at your bank to compare with.
What I found was quite strange.
To begin with, I had to go back to the place where the correct values were.
What I then noticed was that the connection between the cell with the correct value and the cell that retrieved its value from that cell was not working correctly:
The value was retrieved from the correct address, i.e. from the correct cell, it was clear and obvious). But the values that were provided were wrong!
For example: the value in cell F30 in the “Dec” tab for the year 2025 is 2900.90. But when that value is retrieved and then displayed in cell F24 in the “Jan” tab for the year 2026, the value is delivered as 435.56.
I double-checked that it was actually the correct path to the cell where the value was to be retrieved. It was the correct address.
Mysterious.
I then deleted the entire path in the cell that was to retrieve the value and entered it again. That is, the exact same path to the same cell.
Now it worked!
Why?
No idea.
In this example, I actually had to go back to spreadsheets for years that I hadn’t had open in a very long time before I could finally find the cell where the error originated.
It also differed between different accounts how far I had to go back. For a certain account I had to go back as far as 2021, for another account it was the sheet for 2023.
After that I was forced to do the same procedure for each year between the months of December and January for each new change from an older year to the next year, all the way up to the most recent year (2026 in this case).
That is, in this case there were no changes whatsoever made to the sheets where the “errors” had their source. The calculation error occurred, although it is hard to believe, actually in LibreOffice Calc.
Since this has happened a number of times, I have some routine in finding and correcting errors, but that is not how it is supposed to work, and it is after all quite tiring – and there are better things to do with my time.
The crucial question, the reason I’m posting this thread, is of course: why is this happening, and what can be done to prevent it from happening over and over again?
Looking forward to hearing from all the knowledgeable Calc-Kings 
Kind regards
ICIT
