Issue 114992 - Calc inserts a wrong, warped formula
Summary: Calc inserts a wrong, warped formula
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 3.2.1
Hardware: Unknown Linux, all
: P2 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2010-10-08 15:09 UTC by heaven87
Modified: 2017-05-20 10:44 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Example (6.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-11-07 07:52 UTC, heaven87
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description heaven87 2010-10-08 15:09:04 UTC
At first, to reproduce it, you should download this file —

After that you should try enter the next formula into Sheet3.B2 cell:
1. Select the Sheet3.B2 cell and press "=" button.
2. Switch to the Sheet1, and click(or double click depending on your settings)
B2 cell, to insert its value into the formula.
3. Enter "+" symbol into the formulas field.
4. Switch to the Sheet2, also select the B2 cell, same as in the previous sheet.
5. Switch to the Sheet3 and press "Enter".

So, instead of inserting something like this — "=Sheet1.B2+Sheet2.B2", Calc
inserts something like this — "=sheet2.b2sheet1.b2+"

System is Arch linux (x86_64). Also this bug is reproducible with the
go-openoffice and with openoffice-infra-ru builds.

Best regards.
Comment 1 Raphael Bircher 2010-10-11 21:26:39 UTC
The file is no longer aviable, please attache the file to the issuetracker, thanks
Comment 2 heaven87 2010-11-07 07:52:23 UTC
Created attachment 72887 [details]
Comment 3 heaven87 2010-11-07 07:53:06 UTC
Comment 4 Edwin Sharp 2013-01-25 12:20:46 UTC
The result is =sheet2.b2sheet1.b2+ with the message "Calc found an error in the formula entered. Do you want to accept the correction proposed below?"
=sheet2.b2sheet1.b2 which gives #NAME?
Comment 5 Edwin Sharp 2013-01-25 12:21:37 UTC
... using Rev. 1400866 on Win 7 64.
Comment 6 jkfidel 2013-11-14 21:41:18 UTC
I have attempted to replicate this bug using the following build and configuration:
Apache OpenOffice 4.0.1; AOO401m5(Build:9714)  -  Rev. 1524958
Windows 7 Ultimate 64-bit, Service Pack 1
Intel Core 2 Duo T6600 2.20 GHz, 4GB RAM

I cannot replicate the warped formula results of the OP or comment 4, but there is still unexpected behavior in Calc.

Results of initial attempt to replicate using the attachment in Comment 2 (example.ods) and the steps in Comment 1 follow....

After completing Step 1 and switching to Sheet1, I am unable to select any cell in Sheet1 by clicking.  The formula remains unchanged after clicking cells in Sheet1.  The only cells that can be added to the formula in Sheet3.B2 are those cells in Sheet2 or Sheet3.

I have also attempted some follow-up tests with same spreadsheet but slight modifications.  Details are further below, but here are the results of my tests:

In order for the bug to occur...
1. It seems necessary that the sheet with the formula-in-progress be frozen (Sheet3).
2. It also seems necessary for one of the other sheets to not be frozen.  

Further results:
1. If a sheet is not frozen, we can add a frozen and/or non-frozen sheet's cell to its formula bar by mouse-click.
2. If a sheet is frozen, we can add a frozen sheet's cell to its formula bar by mouse-click, but we CANNOT add a non-frozen sheet's cell to its formula bar by mouse click.

Expected results are received in each test if formulas are entirely typed (no mouse-clicks).

I believe this is clearly unexpected behavior.  By contrast, Microsoft Excel 2010 does not have this problem.

Test Details...
In each of these tests:
A single workbook with three sheets is used.
One or more of the sheets have frozen panes obtained by selecting B2, and  Window->Freeze.
Sheet1.B2 has value 1
Sheet2.B2 has value 2
In Sheet3.B2, we attempt to add the formula '=Sheet1.B2+Sheet2.B2' via....
  1. Type '=' in Sheet3.B2
  2. Switch to Sheet1 and click Sheet1.B2
  3. Type '+'
  4. Switch to Sheet2 and click Sheet2.B2
  5. Press Enter.

The expected result is Sheet3.B2 will display value 3.
Test t1
Sheets not frozen:  2,3
Sheets frozen:  1
Result:  As expected.

Test t2
Sheets not frozen:  1,3
Sheets frozen:  2
Result:  As expected.
Test t3
Sheets not frozen:  1,2
Sheets frozen:  3
Result:  Bug.
Description:  No cell in any sheet other than Sheet3 can be selected by mouse click.
Test t4
Sheets not frozen:  1,2
Sheets frozen:  3
Result:  As expected.
Test t5
Sheets not frozen:  2
Sheets frozen:  1,3
Result:  Bug.
Description:  Sheet1.B2 can be selected by mouse click but Sheet2.B2 cannot.

Test t6
Sheets not frozen:  1
Sheets frozen:  2,3
Result:  Bug.
Description:  Sheet2.B2 can be selected by mouse click but Sheet1.B2 cannot.
Test t7
Sheets not frozen:  -
Sheets frozen:  1,2,3
Result:  As expected.
Comment 7 Marcus 2017-05-20 10:44:24 UTC
Reset the assignee to the default "".