|
Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing |
| Summary: | Wrong cached sheets used in external range references. | ||||||
|---|---|---|---|---|---|---|---|
| Product: | Calc | Reporter: | wbtmagnum <magnum> | ||||
| Component: | programming | Assignee: | oc | ||||
| Status: | CLOSED FIXED | QA Contact: | issues@sc <issues> | ||||
| Severity: | Trivial | ||||||
| Priority: | P3 | CC: | issues, jbf.faure, kyoshida, mdxonefour | ||||
| Version: | OOo 3.1 RC2 | Keywords: | regression | ||||
| Target Milestone: | --- | ||||||
| Hardware: | All | ||||||
| OS: | All | ||||||
| Issue Type: | DEFECT | Latest Confirmation in: | --- | ||||
| Developer Difficulty: | --- | ||||||
| Issue Depends on: | |||||||
| Issue Blocks: | 99999 | ||||||
| Attachments: |
|
||||||
Created attachment 62079 [details]
Example of vlookup not working
Not a P1 Looks like it would be my issue.. *** Issue 105130 has been marked as a duplicate of this issue. *** 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. Lookup into external data is a common use case. Targeting to OOo3.2, blocking issue 99999. In cws calc32stopper3: revision 276771 sc/source/ui/docshell/externalrefmgr.cxx In fact this was even worse and not related to VLOOKUP at all, but happened with every external range reference (not single reference) referring another than the first used sheet. More disturbing than the bug itself, is the fact that this has not been discovered until now... The issue was submitted in May. @nn: well, what I meant was that, assuming that the bug was there from the very beginning of this external ref re-work, which was more than a year ago, it took more than a year for this bug to get discovered despite the common usage scenario. I was just trying to figure out whether I did adequate testing for this usage scenario, or I forgot to test it. That's all. Reassigning to QA for verification. verified in internal build cws_calc32stopper3 *** Issue 107098 has been marked as a duplicate of this issue. *** |
Please assign this problem to V3.1 (it does not exist yet) Situation ========= Spreadsheet 1 (S1) contains several sheets (e.g. 2007, 2008, 2009) containing some data. In my example each row has a unique identifier ("Ref0001", "Ref0002", ...) and some data ("1.1.<year>", "2.1.<year>", ...). Note: <year> is set according to the sheet. Spreadsheet 2 (S2) does lookups in S1 using the function VLOOKUP. Problem ======= As long as you do lookups in one sheet only (e.g. S1.2007), everything works fine. When changing the formula to lookup in another sheet (e.g. S1.2008), somehow calc caches the formula / ignores the updated sheet reference and still fetches data from S1.2007. Examples ======== formula 1: =VLOOKUP("Ref0001";'file:///C:/oo31_test2.ods'#$'2007'.A$1:B$9999;2) result 1: "1.1.2007" formula 2: =VLOOKUP("Ref0001";'file:///C:/oo31_test2.ods'#$'2008'.A$1:B$9999;2) result 2: "1.1.2007" formula 3: =VLOOKUP("Ref0001";'file:///C:/oo31_test2.ods'#$'2009'.A$1:B$9999;2) result 3: "1.1.2007" Result 2 & 3 are wrong and should have returned "1.1.2008" respectively "1.1.2009". Interestingly, when changing the lookup-range, the correct results are returned: formula 4: =VLOOKUP("Ref0001";'file:///C:/oo31_test2.ods'#$'2008'.A$1:B$9000;2) result 4: "1.1.2008" This bug/behaviour did not exist with 3.0.1 and was introduces somewhere between 3.0.1 and 3.1. If needed, I can provide you 2 example files for a better understanding. Best regards, Sascha