Apache OpenOffice (AOO) Bugzilla – Issue 105130
OO0 3.1 Calc: wrong result in formula referencing cells in other file.
Last modified: 2009-09-22 20:58:26 UTC
This problem raises in the 3.1 version. My speadsheet (aa.ods) has formulas (in colum B)referencing values in two different sheets of another .ods file (bb2009.ods). The value result is wrong (see correct values in column C, differences are marked red)). Trying to correct the problem I have re-written the link in the formula and I have noticed some improvement, but only the first time, still having errors anyway. Another attempt was to rename sheets without embedded blanks. This worked only for a while.
Created attachment 64800 [details] zip file containing the two referenced spreadsheets
I ried to checked "testcase.zip" with "Ooo 3.1.1 WIN XP DE-multilingual version German UI activated [OOO310m19 (Build 9420)]" and can confirm that aa.ods in column 'B' shows results different from the results shown in "2.4.1 Multilingual version English UI WIN XP: [680m17(Build9310)]". My knowledge concerning the used formula is too poor to decide what result would be the correct one. Settings in 'Tools -> Options -> Calc -> Calculate' have influence to the result. With my standard settings I (Selected: 'allow regular expression', saw results marked as incorrect in 'C14', 'C16', 'C17', 'C19' I saw something strange: With menu 'Edit - Search and Replace' It sometimes modified the results in column 'B', when I replaced all "=" by "=". Additionally, sometimes modifications in my settings modified the results. but all that not reproducible at all, sometimes changes happen, sometimes they do not happen. @cornix: Please explain what your formulas should do and why the results in column C are the expected ones.
The formula looks in an another sheet to find a value associated to a keyword, indicated in the formula, e.g. the formula in B5 looks in bb2009.ods, sheet "Cornelio 08", for a raw containing "Punto 10"; if it finds it it takes the value in column 9 (B+8-1)(value: 73). In column C of aa.ods I have put the value that really there is in the file bb2009.ods and that the formula returns, but only in previous versions of OOo.
I confirm that changing something in the formula with the same value can have transient effects. I notice furthermore that the wrong results seems to be taken from the wrong sheet ("Cornelio 08" instead of "Isa 08").
I checked with "Ooo 3.1.1 WIN XP DE-multilingual version German UI activated [OOO310m19 (Build 9420)]" and can confirm that the formulas in some cases take data from the wrong sheets. Steps to reproduce: 0. open "aa.ods", "bb2009.ods" In "aa.ods" in 'B5' you will see "73", what is correct. Pls. compare with 'bb2009.ods#Cornelio 08.I8' In "aa.ods" in 'B14' you will see "73", what is NOT correct. Pls. compare with 'bb2009.ods#ISA 08.I8' 1. modify 'bb2009.ods#Cornelio 08.I8' from "73" to "2" 2. Save bb2009.ods 3. in "aa.ods" use menu "Edit -> Links -> Refresh" to read current values from 'bb2009.ods' as expected, 'B5' will change from "73" to "2" unexpectedly. B14 also changes from "73" to "2", although the formula references to sheet 'ISA 08', where no modifications have been one That shows that formula works with data from wrong sheet. That's very very dangerous for users who trust th the results of their spreadsheet. I did not find out why that formula links to data in wrong sheet, I only see that only formulas with reference to matrices starting in second column are affected. @cornix: Do you have any idea concerning this "only formulas with reference to matrices starting in second column are affected" effect?
Probably a duplicate of issue 101639. @cornix : if you modify the range of your matrix the result becomes correct. Regards JBF
@jbfaure: I believe you are right.
@jbfaure: Yes, it is the same problem described in issue 101639. If you have more than one VLOOKUP with the same range (this it is important), each vlookup looks in the sheet indicated in the first used vlookup , even if a different sheet is indicated.
Due to comments from cornix Sat Sep 19 16:20:42 +0000 2009 *** This issue has been marked as a duplicate of 101639 ***
duplicate -> closed