As I stated in the description, the results change based on where you
place the copied Sheet, so you should also try placing the copy before
Sheet1, Sheet3, and at the end. Also in the array portion of the
vlookup, I specified the range as:
Sheet2.$A$1:$B$2
You may want to experiment with:
$Sheet2.$A$1:$B$2, and
'Sheet2'.$A$1:$B$2
I seem to remember them all producing different errors.
-Steve
** Description changed:
Binary package hint: openoffice.org
When copying a sheet that has vlookup functions referencing other
sheets, Calc temporarily shows the wrong data in either the new copy, or
the original sheet. The results vary depending on where you place the
new sheet and whether your array reference uses $Sheet1 or just Sheet1.
Also, the wrong results are only while you have the file open, if you
save, close, and re-open the file, the data is correct in both sheets.
- Steps to reproduce:
+ Steps to reproduce (I'll attach a spreadsheet in which steps 1-4 are
+ already done):
1. Create a new spreadsheet (you should get the default Sheet1, Sheet2,
- Sheet3). I'll attach a spreadsheet in which steps 1-4 are already done.
+ Sheet3).
2. In the top left corner of Sheet2, create the following table of values:
a, 1
b, 2
2. In the top left corner of Sheet3, create the following table of values:
b, 3
a, 4
4. In Sheet1 create the following table:
a, =VLOOKUP($A1;Sheet2.$A$1:$B$2;2;FALSE()),
=VLOOKUP($A1;Sheet3.$A$1:$B$2;2;FALSE())
b, =VLOOKUP($A2;Sheet2.$A$1:$B$2;2;FALSE()),
=VLOOKUP($A2;Sheet3.$A$1:$B$2;2;FALSE())
This will display the following values:
a, 1, 4
b, 2, 3
Which is correct.
5. Right click on Sheet1, and select Move/Copy. Select that you want to
insert before Sheet2, and check the Copy box. Click OK.
6. Observe that the newly created Sheet1_2 has the correct values, while the
original Sheet1 has bad values in column B.
6a. If you delete Sheet1_2, Sheet1 goes back to normal
6b. If you leave Sheet1_2, but save, close and re-open, both sheets will be
fine.
My Version information:
$ lsb_release -rd
Description: Ubuntu 8.04.1
Release: 8.04
$ apt-cache policy openoffice.org-calc
openoffice.org-calc:
Installed: 1:2.4.1-1ubuntu2
Candidate: 1:2.4.1-1ubuntu2
Version table:
*** 1:2.4.1-1ubuntu2 0
500 http://ubuntu.media.mit.edu hardy-updates/main Packages
100 /var/lib/dpkg/status
1:2.4.0-3ubuntu6 0
500 http://ubuntu.media.mit.edu hardy/main Packages
** Tags added: calc copy openoffice reference vlookup
** Tags added: worksheet
--
OpenOffice Calc shows wrong value for vlookup in copied sheet
https://bugs.launchpad.net/bugs/257446
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
