Help with faulty spreadsheet (Vlookup function) in Calc OPENOFFICE.

lancslass2008

Hi all


I am trying to formulate a simple spread sheet to help my OH with his job. Basically we want to create a spreadsheet that lets you select some components from a list of items, then adds the values up and calculates them. So, we learnt the Vlookup function and set about creating a spread sheet. Things seemed ok at first until we discovered that in our Vlookup drop down box we can only select the first 15 items. The 16th item is shown as only half of what it should be. I'll try and explain further...


So, this is a simple single page spread sheet. Thus with the list of components on the same page as the calculator.


So, lets imagine we have a list as follows


London 1
Manchester 2
Birmingham 3
Dorset 4
Spain 5
Luxembourg 6
Edinburgh 7
France 8
Germany 9
Latvia 10
Belgium 11
Romania 12
Hungary 13
Switzerland 14
Slovakia 15
Northampton 16
Wiltshire 17
Durham 18
Newcastle 19


Above is a list of random places. Next to these places is a number I have given them solely to explain my problem.


So, in my spread sheet I have 2 dropdown boxes (Vlookup). The left dropdown box is called Place 1. The right dropdown box is called Place 2.


So, my intention is to let the user click on dropdown box 1 and select an Item from the list, such as Germany (which returns a value of 9). The user then selects an option from dropdown box 2, for example Spain (which would return a value of 5). I then have a calculation box underneath which then returns a sum of 9 + 5. The result being 14. This works perfect for the first 15 items in both of the dropdown boxes. However, for some strange reason BOTH of the drop down boxes only show the first 15 possible selections from my list. The 16th option is cut in half, such as
Northa
You can see that Number 16 should be Northampton but the 'mpton' is missing. Obviously, the list of the items below 16th are missing from BOTH of my drop down boxes. This is really bugging me because the spread sheet obviously works because I can select ANY combination of places from 1 to 15 from both boxes and the sum is always returned correct. Indeed, I can select any of the first 15 places from dropdown box 1 AND any of the first 15 places from dropdown box 2 and they calculate correctly.


What is going on? Does openoffice only allow 15 entries when using the vlookup function?


Thanks all.

Fightsback

Give Libreoffice a try, it's better maintained.

Le_Kirk

Column width might be preventing all of Northampton being shown.
In your drop down list set up have you allowed enough rows (more than 15 in your case) to be shown. Don't know about open office but in MS Excel you have to specify the range of names.

EchoLocation

If your document .xls that might be the problem, try saving and using it as an .ods file.

lancslass2008

Thanks all. Tried your suggestions to no avail . Would anybody be willing to let me Email them this spread sheet and have a look over it for me?

Stompa

It won't help much, but it appears that somebody else has encountered a similar sounding problem. See the comment by 'Real Exam' here:

http://openoffice.blogs.com/openoffice/2007/07/dropdown-lists.html