Formulas are changing unexpectedly. How to debug?

[Mountain]

In my Calc-doc, when I update certain data cells, formulas in other places are unexpectedly changed. I can find no relationship between the data updates and the changing formulas. (EDIT1: I’m not editing formulas, I’m entering data.)

EDIT2: I am adding a lot more detail at the bottom of my question.

This problem started after I added some formulas with INDIRECT and OFFSET. But these formulas do not seem to have any connection or relationship to the cells having the problem. (EDIT: And maybe these new formulas are just a red herring. I mention them only in order to provide all the info I can think of.)

Not all cells change unexpected. Only columns L, M and T show the issue.

EDIT: to be clear, I am not editing any formulas when this strange error happens. All I am doing is entering new data into empty cells on a different sheet. When I come back to Sheet 1, the formulas in certain cells in columns L, M and T (e.g., L6) are now updated to include a reference to the sheet I was editing. But I was not editing those cells in the other sheet (e.g., L6). I was not even editing any formulas at all.

Details of the problem:

Sheet 1, cell L3 has this formula:

=IF(U3>0,(E3+F3+G3+H3+I3+J3+K3+O3)/U3,0)

On Sheet 10 (or about 10 other sheets), if I enter data into cells where there are no formulas, and no references I can find to cell L3 on Sheet 1, the formula above will be changed to:

=IF(U3>0,(sht22.P17+sht22.Q17+sht22.R17+sht22.S17+sht22.T17+sht22.U17+sht22.V17+O3)/U3,0)

I’m having trouble discovering the exact steps to reproduce the problem. The problem happens every single time I edit the Calc-doc. It affects the same cells every time. And I know roughly what causes it. But I don’t know the exact editing change that causes the formula to change. How can I go about tracing this or debugging it?

EDIT2:

More details:

• Calc-doc has 37 sheets.
• I only see the problem in sheet 1.
• Furthermore, I only see the problem in cells L3 to L7, M3 to M7, T3 to T7.
• In those cells, the formulas are changed as shown below. The changes happen in response to entering new data into sheets sht14 to sht 34.
• When entering data, I am not moving data or cells and I am not changing formulas.
• I am pasting data from a plain text document and from a new (unsaved) temporary CSV file opened in another Calc window.
• The new data is pasted into the next row available and it usually goes across columns C to AU.
• Tools > Detective > Trace Precendents does not show any precendents other than those expected by the simple formulas (e.g., =IF(U3>0,(E3+F3+G3+H3+I3+J3+K3+O3)/U3,0))
• I still have no idea why the formulas are changing.

For each cell that is affected, I am showing the original formula and then the changed formula below. (After each edit, I have to manually change the formulas back to the originals.)

L3:

=IF(U3>0,(E3+F3+G3+H3+I3+J3+K3+O3)/U3,0)
=IF(U3>0,(sht22.P17+sht22.Q17+sht22.R17+sht22.S17+sht22.T17+sht22.U17+sht22.V17+O3)/U3,0)

L4:

=IF(U4>0,(E4+F4+G4+H4+I4+J4+K4+O4)/U4,0)
=IF(U4>0,(sht16.P16+sht16.Q16+sht16.R16+sht16.S16+sht16.T16+sht16.U16+sht16.V16+O4)/U4,0)

L5:

=IF(U5>0,(E5+F5+G5+H5+I5+J5+K5+O5)/U5,0)
=IF(U5>0,(sht16.P17+sht16.Q17+sht16.R17+sht16.S17+sht16.T17+sht16.U17+sht16.V17+O5)/U5,0)

L6:

=IF(U6>0,(E6+F6+G6+H6+I6+J6+K6+O6)/U6,0)
=IF(U6>0,(sht20.P15+sht20.Q15+sht20.R15+sht20.S15+sht20.T15+sht20.U15+sht20.V15+O6)/U6,0)

L7:

=IF(U7>0,(E7+F7+G7+H7+I7+J7+K7+O7)/U7,0)
=IF(U7>0,(sht27.P16+sht27.Q16+sht27.R16+sht27.S16+sht27.T16+sht27.U16+sht27.V16+O7)/U7,0)

L8:

=IF(U8>0,(E8+F8+G8+H8+I8+J8+K8+O8)/U8,0)

no change after editing

L9:

all normal from L8 down; no change after editing

M3:

=IF(U3>0,(H3+I3+J3+K3+O3)/U3,0)
=IF(U3>0,(sht22.S17+sht22.T17+sht22.U17+sht22.V17+O3)/U3,0)

M4:

=IF(U4>0,(H4+I4+J4+K4+O4)/U4,0)
=IF(U4>0,(sht16.S16+sht16.T16+sht16.U16+sht16.V16+O4)/U4,0)

M5:

=IF(U5>0,(H5+I5+J5+K5+O5)/U5,0)
=IF(U5>0,(sht16.S17+sht16.T17+sht16.U17+sht16.V17+O5)/U5,0)

M6:

=IF(U6>0,(H6+I6+J6+K6+O6)/U6,0)
=IF(U6>0,(sht20.S15+sht20.T15+sht20.U15+sht20.V15+O6)/U6,0)

M7:

=IF(U7>0,(H7+I7+J7+K7+O7)/U7,0)
=IF(U7>0,(sht27.S16+sht27.T16+sht27.U16+sht27.V16+O7)/U7,0)

M8 and down:

=IF(U8>0,(H8+I8+J8+K8+O8)/U8,0)
(all normal; formulas do not change)

Columns N, O, P, Q, R, S are all normal

T3:

=E3+F3+G3+H3+I3+J3+K3+P3+R3
=sht22.P17+sht22.Q17+sht22.R17+sht22.S17+sht22.T17+sht22.U17+sht22.V17+P3+R3

T4:

=E4+F4+G4+H4+I4+J4+K4+P4+R4
=sht16.P16+sht16.Q16+sht16.R16+sht16.S16+sht16.T16+sht16.U16+sht16.V16+P4+R4

T5:

=E5+F5+G5+H5+I5+J5+K5+P5+R5

changes after editing similar to above

T6:

=E6+F6+G6+H6+I6+J6+K6+P6+R6

changes after editing similar to above

T7:

changes after editing similar to above

T8 and below:

(all normal; formulas do not change)

[Mountain]

I have never been able to solve this. It remains a perplexing problem.

[mariosv]

Seems as if you have selected both sheets when you enter the formula, in that case it’s inserted in all selected sheets at the same time.

[Mountain]

That’s not the case. I’ll edit my question to make it more clear. Thank you.

[mariosv]

Can then please upload a minimal sample file where to reproduce the issue, with a step by step explanation?

[Mountain]

Unfortunately, no. My question is “how to figure out how to reproduce it?” If I could produce a minimal sample where the issue is reproduced, I’ll be able to solve it myself. I have a programming background. And unfortunately, I cannot even upload the actual spreadsheet as it is a client confidential financial doc. The more I see this problem in action, the more convinced I am that I have stumbled upon a Calc bug.

[mariosv]

While press [Ctrl] click on a couple of sheet tabs, both are selected, what you write in one of them is also write in the other.

[Mountain]

This is not an issue of having more than one sheet tab selected. You can rest your mind about that. That is not the problem here. Thanks.

[mariosv]

Perhaps resetting the User profile

[PYS]

Hi

If I met this problem I would check, to be sure, if the relevant cells are considered “related”. For each I would do Tools Detective Trace Precedents (& Dependants).

[EDIT]

The tool detects the relations between sheets. The arrows are different, they terminate with a square.

[End of EDIT]

You do not indicate if the document includes macros. If yes, of course I would start by opening the document by disabling them to see if it changes anything.

You assume a bug in Calc. It is always possible but unlikely for this type of problem would be systematic. If I understand you have this problem for this spreadsheet and not all cells. It is therefore possible that there is a problem in this spreadsheet (I’ve seen a Writer document where the styles subfile was found in the content subfile following a saving problem). Elucidating such problems requires to unzip the file and inspect the xml content, which is not easy …

A one last assumption could be the system. Restart the computer may solve aberrant behavior.

[EDIT#2]

This type of change could be normal if the modified sheet is used in a scenario. The spreadsheet includes scenarios? You can verify this by displaying the Navigator (sidebar or F5) with the Scenario tool:

HTH - Regards

[Mountain]

Yes, that helps. I will learn about the Tools > Detective feature. At this moment, I am not sure how to use it. This calc-doc contains no macros. I have restarted my computer and the problem persists. I’m running Kubuntu. Thanks again.

[Mountain]

It seems that Tools > Detective > Trace Precedents won’t help me because it doesn’t work across multiple sheets. My problem happens when I’m editing sheet 10, cells in sheet 1 are changed. (BTW, protecting sheet 1 doesn’t help.) Calc trace error across multiple sheets

[PYS]

This tool can help. I saw that your problem was between different sheets. See my edit in my answer.

[Mountain]

Thanks for your answer. I like this tool. It doesn’t show me any problem in this calc-doc, but I was going to upvote your answer anyway. However, I don’t have enough karma to upvote. Sorry.

[Mountain]

Thanks for updating your answer. There are no scenarios in this Calc-doc. I verified that using the method you suggested. Also, I see no problems when using the Detective Trace tools. The strange problem continues.

[PYS]

I saw that the document was confidential but could you not anonymize? Edit Find & Replace, Search for: [:alpha:], Replace with: x, Other options: check Regular expressions, Search in Values, Click Replace All

[Mountain]

Upgrading to LO 5.0 seems to have resolved this for me. However, at least on Kubuntu 12.04, LO 5.0 seems very rough. Font display is horrible (at least on my machine). Anyway, I am thrilled that this bug has gone away and I hope it is permanently gone.