[Issue] Changing value of cell changes formula in another??

[Joseph DuVivier]

In Calc can changing the content of one cell actually change the formula (not just the value returned) in another cell? I wrote an ods spreadsheet which is failing because, when I change a value in one cell, it actually changes a constant in a formula in another cell. I didn't think this was possible. Interestingly this seems only to happen in one case where I copied the formula to a cell and not in many other cases in which I copied the same formula to other cells.

The formula changes from:
{=IF(CP35<>"";CP35;IF(DV36<>"";DV36;IF(SUM(DQ34:DS36="")=0;"E";IF(SUM(DQ34:DS36="")=1;IF(DQ34="";1;IF(DR34="";2;IF(DS34="";3;IF(DQ35="";4;IF(DR35="";5;IF(DS35="";6;IF(DQ36="";7;IF(DR36="";8;IF(DS36="";9;"")))))))));""))))}
to:
{=IF(CP35<>"";CP35;IF(DV36<>"";DV36;IF(SUM(DQ34:DS36="")=0;"E";IF(SUM(DQ34:DS36="")=1;IF(DQ34="";1;IF(DR34="";2;IF(DS34="";3;IF(DQ35="";4;IF(DR35="";5;IF(DS35="";9;IF(DQ36="";7;IF(DR36="";8;IF(DS36="";9;"")))))))));""))))}

I.e. the "6" just after {DS35="";} changes to a "9". This occurs when the value of DS35 is changed (indirectly). Interestingly <cntl> Z does not undo the formula change.

I tried this using OpenOffice 3.4.1 on both a WIndows XP laptop and and OSX Mac. Same result.

Attached is the spreadsheet. Most cells are protected to prevent accidental changes, but there is no password. Just follow the instructions in the first cell to replicate the problem.

Thanks to anyone who can help.

[Charlie Young]

I suppose it must be some subtle bit of corruption in an extremely complex spreadsheet. Note that the formula in your example is in DV35, not DV32, and the cell to change is E11, not E8. You must have inserted 3 rows and forgotten to change these references. If I copy that formula by going to DV35, hit F2 Ctrl+Home Ctrl+ End, then Ctrl + C to copy, then hit ESC, then backspace to delete the formula, then F2 Ctrl+V to reenter it, and then Ctrl+Shift+Enter (since it's an array formula), the problem disappears. It would be nice to find the exact problem, and I'm still looking.

Meanwhile...

Now, if 6 turned out to be 9,
I don't mind, I don't mind.
If all the hippies cut off their hair,
I don't care, I don't care.
Dig, 'cos I got my own world to live through
And I ain't gonna copy you.

[Charlie Young]

I haven't found the exact problem yet, but I think I'm making some progress. I'll report on what I've found so far to try to help others who may be struggling with this.

No, it isn't haunted by the ghost of Jimi Hendrix (I don't think).

First of all, the spreadsheet was saved with the evil Tools > Options > Calc > Calculate > Automatically find column and row labels checked. Unchecking this and saving the spreadsheet doesn't by itself fix the problem, but I suspect that this is somehow the source of the difficulty. Some discoveries:

1) Changing the 9 to a 6 in E11 changes the "6" to a "9" in CQ34.

2) The changing number in the formula in DV35 will change to any number, even negative and fractional ones, entered into cell CP35, but CP35 itself contains a formula which ultimately traces back to J11. So initially at least, the DV35 formula number changes to any numeric entry in J11, although at first j11 is blank!

3) So, if I enter in J11 the formula

Code: Select all

=PI()

the formula in DV35 becomes

Code: Select all

=IF(CP35<>"";CP35;IF(DV36<>"";DV36;IF(SUM(DQ34:DS36="")=0;"E";IF(SUM(DQ34:DS36="")=1;IF(DQ34="";1;IF(DR34="";2;IF(DS34="";3;IF(DQ35="";4;IF(DR35="";5;IF(DS35="";3.14159265358979;IF(DQ36="";7;IF(DR36="";8;IF(DS36="";9;"")))))))));""))))

This is certainly very interesting.

[Charlie Young]

I think I've got this simplified to the point where the bug (it is a bug, I'm pretty sure), is more transparent. I now do not think it has anything to do with Tools > Options > Calc > Calculate > Automatically find column and row labels (though that option is still to be used advisedly).

Cell A1 starts out blank.

B1 starts with the array formula (I omit the curly brackets here)

Code: Select all

=IF(A1<>"";A3;IF(A2="";6;""))

A3 starts with the number 9.

Enter 9 in A1, and the formula in B1 changes to

Code: Select all

=IF(A1<>"";A3;IF(A2="";9;""))

But note that it actually seems to be the case that the formula result (9) is what is appearing in the THEN part of the second IF statement, since if I then change A3 to another number, say 2, the formula changes to

Code: Select all

=IF(A1<>"";A3;IF(A2="";2;""))

The file is attached. The numbers 6 and 9 are irrelevant, but note that the formula will not change the THEN to a text value if I enter such a thing in A3.

Sometimes the behavior reverts to "normal," and I have to do File > Reload to get it to act up again.

I post it to be sure others see the same behavior, and maybe note some quirks that I have missed.

[acknak]

Nice work!

I see the same problem testing your sample sheet with AOO4RC on Linux--it looks like a pretty stable bug.

Sometimes the behavior reverts to "normal," and I have to do File > Reload to get it to act up again.

It does this for me any time I edit and re-enter the formula (without actually changing the formula, that is). Maybe it's something in the way the formula is saved/loaded as a file that's causing the problem.

Edit: PS:

But if I edit & re-enter the formula, then save the "modified" file and reload, now it shows the problem again. So it seems to be something in saving or loading the sheet that leads to the problem.

[Charlie Young]

It can be simplified further.

Again, start out with A1 blank, then in A2 put the array formula

Code: Select all

=IF(A1<>"";A3;6)

Start A3 off as 9 (or any other number), then enter something in A1.

It seems to have something to do with the fact that it's an array formula, but I don't quite see what it could be exactly.

[acknak]

Nice. Works for me, too.

A1 has to be empty and after entering the array formula and the file has to be saved and reloaded to see the change.

This formula also "works" for me: =IF(A1;A3;6)

but not AND(A1;A3;6) or OR(A1;A3;6).

So maybe it's something specific to IF?

[keme]

I can't repeat that behavior except when both cells are selected, as detailed in my response in the other forum. Clearly not desirable behavior, though, so the "bug" tag fits.

[Joseph DuVivier]

Thank you guys for looking into this problem. It's good to know I wasn't entirely crazy. I was able to replicate your results using the if6was9_v2.ods file. Also the problem doesn't appear if =IF(A1<>"";A3;6) is not entered as an array formula. Also it does seem to be true that the cell of the first argument of the IF has to be empty and the file has to be saved and reloaded to reproduce the change in the third argument of the IF.

[acknak]

Anyone up for submitting a bug report?

[Charlie Young]

Anyone up for submitting a bug report?

122831

[acknak]

Nice! I added a comment to get on the cc list.

Thanks, Charlie!