[Bug 381543] Re: OpenOffice Calc VLOOKUP function doesn't work like Excel
** Also affects: libreoffice (Ubuntu) Importance: Undecided Status: New ** Description changed: Binary package hint: openoffice.org - When using the VLOOKUP function, the second parameter is interpreted as an array in OpenOffice, but Excel treats it as a range. This means that Excel spreadsheets that use a single cell reference will work in Excel, but not in OpenOffice. It should be possible to fix this by treating a single cell reference as a single element array. - Test case attached. + 1) lsb_release -rd + Description: Ubuntu 11.04 + Release: 11.04 + + 2) apt-cache policy libreoffice-calc + libreoffice-calc: + Installed: 1:3.3.2-1ubuntu5 + Candidate: 1:3.3.2-1ubuntu5 + Version table: + *** 1:3.3.2-1ubuntu5 0 + 500 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main i386 Packages + 100 /var/lib/dpkg/status + 1:3.3.2-1ubuntu4 0 + 500 http://us.archive.ubuntu.com/ubuntu/ natty/main i386 Packages + + 3) What is expected to happen in LibreOffice Calc via the Terminal: + + cd ~/Desktop && wget + https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/381543/+attachment/596214/+files/vlookup%20testcase.xls + && localc -nologo vlookup\ testcase.xls + + is the file obtains the same results as Excel or Gnumeric using VLOOKUP. + + 4) What happens instead is it does not. + + WORKAROUND: Use Gnumeric. + + apt-cache policy gnumeric + gnumeric: + Installed: 1.10.13-1ubuntu1 + Candidate: 1.10.13-1ubuntu1 + Version table: + *** 1.10.13-1ubuntu1 0 + 500 http://us.archive.ubuntu.com/ubuntu/ natty/universe i386 Packages + 100 /var/lib/dpkg/status + + Original Reporter Comments: When using the VLOOKUP function, the second + parameter is interpreted as an array in OpenOffice, but Excel treats it + as a range. This means that Excel spreadsheets that use a single cell + reference will work in Excel, but not in OpenOffice. It should be + possible to fix this by treating a single cell reference as a single + element array. ProblemType: Bug Architecture: amd64 DistroRelease: Ubuntu 9.04 Package: openoffice.org-core 1:3.0.1-9ubuntu3 ProcEnviron: - PATH=(custom, no user) - LANG=en_AU.UTF-8 - SHELL=/bin/bash + PATH=(custom, no user) + LANG=en_AU.UTF-8 + SHELL=/bin/bash SourcePackage: openoffice.org Uname: Linux 2.6.28-11-generic x86_64 ** Tags added: lo33 ** Changed in: libreoffice (Ubuntu) Importance: Undecided => Medium ** Changed in: libreoffice (Ubuntu) Status: New => Triaged ** Summary changed: - OpenOffice Calc VLOOKUP function doesn't work like Excel + Calc VLOOKUP function doesn't produce same results as Excel -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/381543 Title: Calc VLOOKUP function doesn't produce same results as Excel -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 381543] Re: OpenOffice Calc VLOOKUP function doesn't work like Excel
** Tags added: jaunty -- OpenOffice Calc VLOOKUP function doesn't work like Excel https://bugs.launchpad.net/bugs/381543 You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 381543] Re: OpenOffice Calc VLOOKUP function doesn't work like Excel
** Also affects: openoffice Importance: Undecided Status: New ** Changed in: openoffice.org (Ubuntu) Importance: Undecided => Low ** Changed in: openoffice.org (Ubuntu) Status: Confirmed => Triaged -- OpenOffice Calc VLOOKUP function doesn't work like Excel https://bugs.launchpad.net/bugs/381543 You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 381543] Re: OpenOffice Calc VLOOKUP function doesn't work like Excel
** Changed in: openoffice.org (Ubuntu) Status: New => Confirmed -- OpenOffice Calc VLOOKUP function doesn't work like Excel https://bugs.launchpad.net/bugs/381543 You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 381543] Re: OpenOffice Calc VLOOKUP function doesn't work like Excel
Well, I don't really want to get into the question of who is following the spec the closest, because we all know that MS and specs don't mix :) I can't see a big problem with accepting a single value in this function, and it would help compatibility with Excel, which is good for users. I should also point out that I found this bug trying to edit my timesheet at work, so this is a problem that happens in the real world. Also, Gnumeric doesn't have this problem, so making this change wouldn't affect compatibility with that. -- OpenOffice Calc VLOOKUP function doesn't work like Excel https://bugs.launchpad.net/bugs/381543 You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 381543] Re: OpenOffice Calc VLOOKUP function doesn't work like Excel
** Tags removed: amd64 -- OpenOffice Calc VLOOKUP function doesn't work like Excel https://bugs.launchpad.net/bugs/381543 You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 381543] Re: OpenOffice Calc VLOOKUP function doesn't work like Excel
I'm not an expert on this, thought after reading http://office.microsoft.com/en-us/excel/HP100698351033.aspx I get the impression that openoffice behaves more closely to the ms vlookup specification than excel. -- OpenOffice Calc VLOOKUP function doesn't work like Excel https://bugs.launchpad.net/bugs/381543 You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 381543] Re: OpenOffice Calc VLOOKUP function doesn't work like Excel
** Attachment added: "The output should be 'dog' in both columns" http://launchpadlibrarian.net/27259986/vlookup%20testcase.xls ** Attachment added: "Dependencies.txt" http://launchpadlibrarian.net/27259987/Dependencies.txt -- OpenOffice Calc VLOOKUP function doesn't work like Excel https://bugs.launchpad.net/bugs/381543 You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs