To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=106230 Issue #|106230 Summary|openoffice calc sumproduct, lookup, vlookup, Hlookup p |roblems Component|Spreadsheet Version|OOo 3.1.1 RC1 Platform|PC URL| OS/Version|Linux Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P1 Subcomponent|code Assigned to|spreadsheet Reported by|gummibj
------- Additional comments from [email protected] Fri Oct 23 18:31:26 +0000 2009 ------- I have a sheet with many sumproducts (100+) and 13 tabs. 3.0.1 handles them correctly but 3.1.1 mishandles them. I am collating data in many rows from external .xls sheets that my accountant sends me. e.g. one row contains x data for each department, next row y data etc. In 3.0.1 it collects the right data but 3.1.1 will in many cases return the wrong data. It never consist of arbitrary data but mostly copies the data from the same tab as the row above it even though I define it correctly e.g. I use sumproduct in the following way (Path deleted): =SUMPRODUCT('file:///07-aug_journal.xls'#$BT.$C$2:$C$50=P$2,'file://07-aug_journal.xls'#$BT.$D$2:$D$50) Might return the figures from the Store or Bakery tab depending on what is above. In thinking of it, it appears that Sumproduct only works once per column and then it just copies the rest (But I might be wrong Further more, if I start using lookup or vlookup more bizare things come up. If I only use lookup or vlookup the same problem happens but if I alternate the fomulas it works ok. The sumproduct problem only seems to happen if I am collecting data from tabs that have only one possible reference and outcome. Not if it has to compare 2 or more references and sum up. The steps I have taken so far are: - purged config files (Home and system wide) and reinstalled - gone back and forth from 3.0.1 to 3.1.1 cleaning the home config's first - latest was to upgrade from 3.0.1. Opening up the file first to make sure the data was correct and not save the file. Then upgrade and deleting ~/.openoffice and opening it up with 3.1.1 (results same, just by opening up the file it displayed the wrong data. And then saving the file, opening it up again and still the same problem. In all cases the results were the same. I would send the file with this but I am afraid that it is strictly confidential. If there is interest for it I could send samples but I would have to be asked. ProblemType: Bug Architecture: i386 DistroRelease: Ubuntu 9.04 Package: openoffice.org-core 1:3.1.1-1ubuntu1~jaunty1 ProcEnviron: LANGUAGE= PATH=(custom, no user) LANG=en_GB.UTF-8 SHELL=/bin/bash SourcePackage: openoffice.org Uname: Linux 2.6.28-15-generic i686 UnreportableReason: This is not a genuine Ubuntu package --------------------------------------------------------------------- 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]
