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: 4.2.1.1 release Last worked in: 4.1.5.3 release
I can confirm that the reported error is reproducible using the attached document. XP & 4.2.0.4. 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. Cheers
for me also not reproducible with a new file using LO 4.2.2.1 (Win 8.1)
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.
Created attachment 96218 [details] Screenshots of the issue Added screenshots
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
@Ishan: Thank you for your feedback. I tried it now again and now I can reproduce this issue (LO 4.2.2.1, 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".
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. Regards, Ishan.
This is already fixed in the most recent version of 4.2. *** This bug has been marked as a duplicate of bug 75950 ***
Thank you. I have downloaded the latest version, and it works fine.
Migrating Whiteboard tags to Keywords: (possibleRegression) [NinjaEdit]