Issue 101639 - Wrong cached sheets used in external range references.
Summary: Wrong cached sheets used in external range references.
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 3.1 RC2
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
Keywords: regression
: 105130 107098 (view as issue list)
Depends on:
Blocks: 99999
  Show dependency tree
Reported: 2009-05-07 13:02 UTC by wbtmagnum
Modified: 2017-05-20 11:42 UTC (History)
4 users (show)

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

Example of vlookup not working (27.85 KB, application/x-compressed)
2009-05-07 16:28 UTC, wbtmagnum
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description wbtmagnum 2009-05-07 13:02:09 UTC
Please assign this problem to V3.1 (it does not exist yet) 

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. 

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. 

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 

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, 
Comment 1 wbtmagnum 2009-05-07 16:28:49 UTC
Created attachment 62079 [details]
Example of vlookup not working
Comment 2 eric.savary 2009-05-08 18:48:49 UTC
Not a P1
Comment 3 ooo 2009-06-26 14:23:16 UTC
Looks like it would be my issue..
Comment 4 Rainer Bielefeld 2009-09-19 17:58:54 UTC
*** Issue 105130 has been marked as a duplicate of this issue. ***
Comment 5 cornix 2009-09-21 14:08:37 UTC
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.
Comment 6 ooo 2009-09-22 15:27:10 UTC
Lookup into external data is a common use case. Targeting to OOo3.2, blocking
issue 99999.
Comment 7 ooo 2009-10-07 23:50:29 UTC
In cws calc32stopper3:

revision 276771

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.
Comment 8 kyoshida 2009-10-08 13:58:08 UTC
More disturbing than the bug itself, is the fact that this has not been
discovered until now...
Comment 9 niklas.nebel 2009-10-08 14:36:03 UTC
The issue was submitted in May.
Comment 10 kyoshida 2009-10-08 14:55:15 UTC
@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.
Comment 11 ooo 2009-10-13 18:39:57 UTC
Reassigning to QA for verification.
Comment 12 oc 2009-10-16 14:44:10 UTC
verified in internal build cws_calc32stopper3
Comment 13 Regina Henschel 2009-11-21 21:08:51 UTC
*** Issue 107098 has been marked as a duplicate of this issue. ***