Calc - unexpected cell values change adding new sheets

English
, , , ,
#1

Hi,

I’m working with Libreoffice 5.0.1 both on windows 10 and Ubuntu 14.04. I’ve updated from Libreoffice 4.04 on Ubuntu. The problem that I face on every version and OS is the following. My ods is organized as follows

Categories sheet
Filters sheet
Results sheet

In the result sheet there are 3 cells, among others, that make references to the Categories and Filters sheets. If I add a new sheet, e.g. Graphs, the values on these 3 cells change without any logic. This mess happened after a crash of Libreoffice 4.04 on Ubuntu. I tried to update to 5.1 but the problem is still there. Is my ods corrupt? Is this a bug? How can I solve the problem without to muche hassle?

Thanks,
Luca

#2

Info needed.
It will not make any sense to guess what happened during the crash in detail.
If the issue definitely not is caused by wrongly relative sheet addressing , I only can advise to create a new user profile as described in this wiki page. Make sure to have read every word there before acting. Otherwise you may lose relevant content (templates, BASIC code, …) of the profile.

It is incomprehensible for what reason a crash on one system should afflict another system, except you are transferring (parts of) your user profile from one to the other or accessing the same one via a network …

(Editing with respect to the recent update of the question:)
If you got a state showing the correct (and expected) results you may try the following: Copy your sheets all at once (in one go) to a new “untitled” spreadsheet document, and delete the empty original sheets there subsequently. I wouldn’t build my hopes much on this, however.

#3

It’s very difficult to provide examples. Anyway, I’ve noticed that “touching” the formulas in the cells that give me illogical results (without making any change) fixes the values temporarily, until I, for example, copy the Results sheet to Results_2, in which case the values in Results become wrong, and the values in Results_2 are right. Copying Results_2 to Results_3 fixes the values in Results, but breaks the values in Results_2. Very strange!

#4

The fact that the problem occurs on every system makes me think that the problem is in the file being corrupted.

#5

@thze_eraser: This (first comment here) doesn’t make sense to me. Did you check sheet addressing for the formulae producing unexpected results? It is pointless to guess about possible details of a supposed file corruption without having the file at hand.

#6

I’ve checked the addressing many times. None of the formulae makes any reference to Results or Results_2 etc. but only to Categories and Filters that are untouched. Unfortunately I can’t share the file since they are identification data. I’ll keep trying and see if I can replicate the bug to share the file.

#7

(@the_eraser: Your comment crossed an editing of mine which was suppressed by that subsequently.)
It is not very likely that a file corruption should leave intact XML files which LibO would open without alerting about something.
Concerning the addressing of sheets the substantial point in this case should be that it is absolute.
Another hunch: Did you miss to mention that you are using an alien file format (xlsx e.g.)?

#8

I’m using ods and all the relevant addresses are absolute. I was thinking of trying to create a new spreadsheet document and doing “Sheet - Insert sheet from file…”. What do you mean with “Copy your sheets all at once (in one go)”? Selecting all the sheets (Ctrl-click) from the bottom, right-click and “Copy”?

#9

Selecting all the sheets (Ctrl-click) from the bottom, right-click and “Copy”? go to the new item ‘Sheet’ of the main menu, choose ‘Move or Copy Sheet…’ and proceed in the intuitive way.
(Just looked back and found your version given as 5.0.1 - the very first version of the V5 branch; supposed to be rather buggy. You may have to update to 5.1 to find the new menu item. I cannot tell for sure if the older vesrions offer the functionality via the context menue of a sheet tab.)

#10

With respect to Win: There are also portable versions of LibO not requiring an installation. You may use one for testing.
LibO versions packed in the PortableApps self-extracting format are available from PortableApps (1 or 2) or from the download archive of LibreOffice (many).

#11

Exporting the sheets in a new spreadsheet and making everything consistent and relative to that document doesn’t solve the problem. I’ve also noticed that the problem occurs only on cells G2, H2, I2. If I copy and past the formulae anywhere else, the problem seems not occurring. I’ve checked again, Libreoffice on Ubuntu is 5.1.1.2 and on Windows is 5.0.1.

#12

Besides, I’ve noticed the problem sussist only if I add a sheet after the Results sheet. If I add a sheet before, the problem doesn’t occur.

#13

In the end I’ve imported only the sheets which work in a new file and redone my work by hand. The mistery is unresolved.

#14

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.