Download it now!
Bug 76380 - EDITING: Reference to another sheet's cell in the same XLS / XLSX gets invalidated next time when we open file
Summary: EDITING: Reference to another sheet's cell in the same XLS / XLSX gets invali...
Status: RESOLVED DUPLICATE of bug 75950
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
Whiteboard: BSA
Keywords: possibleRegression
Depends on:
Reported: 2014-03-20 03:17 UTC by Ishan Mehta
Modified: 2015-12-15 10:55 UTC (History)
0 users

See Also:
Crash report or crash signature:

Example xlsx file with 2 sheets, one having cell that references a cell in another sheet (5.50 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-03-20 03:17 UTC, Ishan Mehta
Screenshots of the issue (237.34 KB, image/png)
2014-03-23 02:17 UTC, Ishan Mehta

Note You need to log in before you can comment on or make changes to this bug.
Description Ishan Mehta 2014-03-20 03:17:09 UTC
Created attachment 96078 [details]
Example xlsx file with 2 sheets, one having cell that references a cell in another sheet

Problem description: 

Steps to reproduce:
1. Open a new document
2. Have 2 sheets in the document
3. In Sheet2, have some cell with some value
4. In Sheet1, refer that cell from Sheet2 (e.g. =Sheet2.B2)
5. Save the doc as .xls or .xlsx (with .ods, it works without any problem)
6. Close the document
7. Open the same document again, and the reference in Sheet1, the cell's reference would have changed to =#REF!!... (e.g. =#REF!!B2). The cell still shows the value from Sheet2, but if any change is done in that cell in Sheet2, there is no reflection in Sheet1 of that change.
8. Later if we do any change in the Sheet2 and Sheet1 gets recalculated, the cell with formula in Sheet1 becomes 

Current behavior:
The references should remain intact.  It was working fine in 4.1.x.
I have seen this happening on Windows 7 and 8 both.
In case of complex sheet with lot of references, any change in sheet that triggers recalculations, sometimes makes the cells with such referenced formula to be #REF and not even the original value.
If changes happen in second sheet, those are not reflected in Sheet1.

Expected behavior:
The references should be retained and all the recalculations should work properly and the new values should be reflected in the cells which have references to cells from other sheet.
Operating System: Windows 7
Version: release
Last worked in: release
Comment 1 Tim Lloyd 2014-03-22 05:09:24 UTC
I can confirm that the reported error is reproducible using the attached document. XP & However...

Setting another cell in sheet1 = to the same cell in sheet2 does not reproduce the problem
Setting a cell in sheet1 = to any cell in sheet2 does not reproduce the problem
Creating a new doc and  referencing between sheets does not reproduce the problem.

I used xslx docs all the way through my testing.

Comment 2 A (Andy) 2014-03-22 09:20:03 UTC
for me also not reproducible with a new file using LO (Win 8.1)
Comment 3 Ishan Mehta 2014-03-23 01:59:07 UTC
Did you create a document, save it close it and then reopen it?

While you have just added a reference to a cell from another sheet, it works as the reference is still in memory.

But when you close the document and then reopen it, then that reference becomes invalid.

And this happened with me @ my home on Windows 7, and the same thing is also happening @ my office on Windows 8.  So, it seems it is not related to a single installation.
Comment 4 Ishan Mehta 2014-03-23 02:17:04 UTC
Created attachment 96218 [details]
Screenshots of the issue

Added screenshots
Comment 5 Tim Lloyd 2014-03-23 05:13:19 UTC
point taken about memory

Dell Laptop running XP

create a fresh ODS doc
3 cells populated on sheet2
1 cell on sheet1 (E9) is set =sheet2.B20
Save xlsx
Close the doc and reopen. Change B20. This is echoed back into the cell sheet1.E9
Save, exit, shutdown laptop
after reboot, change B20 again and this is correctly reflected back into sheet1.E9

Investigating further I downloaded again the Example.xlsx file attached to this bug
Deleted the reference to sheet2 in cell sheet1.b2 and reinserted the equation =sheet2.b2
save, exit and shutdown
after reboot sheet1.b2 is still =sheet2.b2. Changing the latter value is correctly reflected in sheet1.b2
Comment 6 A (Andy) 2014-03-23 08:35:55 UTC
@Ishan: Thank you for your feedback.

I tried it now again and now I can reproduce this issue (LO, Win 8.1).  But it happens only after saving it as a xlsx file and closing it.  Before it is not saved and closed I can not see this bug.

Steps Done:
1. Open CALC
2. Add a second sheet
3. Go to sheet 2 and write "1" in cell A1
4. Go to sheet 1 make a reference to cell A1 in sheet 2 ("=Sheet2.A1")
5. Go to FILE -> SAVE AS and save it as xlsx
6. Close LO
7. Reopen the saved file
8. Go to sheet 1 cell A1

Result: Cell A1 in sheet 1 shows 1 as output but in the cell "=#REF!!A1".
Comment 7 Ishan Mehta 2014-03-23 12:19:51 UTC
Hi Andy,
Thanks for confirming. Yes, you have to save, close and then reopen to reproduce. Those were steps 5, 6 and 7 in my original description.

Comment 8 Kohei Yoshida 2014-04-25 16:05:22 UTC
This is already fixed in the most recent version of 4.2.

*** This bug has been marked as a duplicate of bug 75950 ***
Comment 9 Ishan Mehta 2014-04-27 02:03:20 UTC
Thank you.  I have downloaded the latest version, and it works fine.
Comment 10 Robinson Tryon (qubit) 2015-12-15 10:55:17 UTC
Migrating Whiteboard tags to Keywords: (possibleRegression)