To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=101639 Issue #|101639 Summary|VLOOKUP does not correctly resolve references to sheet |s in other files Component|Spreadsheet Version|OOo 3.1 RC2 Platform|Unknown URL| OS/Version|Windows XP Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P1 Subcomponent|editing Assigned to|spreadsheet Reported by|wbtmagnum
------- Additional comments from [email protected] Thu May 7 12:02:09 +0000 2009 ------- 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 --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
