List Box inserts apostrophe before data in Linked cell

[lemurman]

Hello everyone,

I have a spreadsheet that has a table of data. Elsewhere in the sheet, there is a row of cells that uses the VLOOKUP function to select some of the data. I am trying to get a list box to modify the contents of the index cell for the VLOOKUP function, but it returns the number that I want preceded by an apostrophe. This apostrophe makes VLOOKUP not work properly. Is this a bug, or is there a way to get it to work properly?

The list box looks like it should operate properly. The Source Cell Range of the list box is given by the range of cells that composes the first column of the table of data. When I click on the down arrow to open the box, all I see are the numbers (1-50) that I should see.

I'm using OpenOffice.org 2.3 (1:2.3.0-1ubunut4)

Thanks!

[huwg]

The apostrophe is Calc's way of denoting text input in a numerically formatted cell. Check the formatting of the source range - it sounds like you will find it is text whilst the destination cell is formatted number.

Edit. From my notes, some of which were probably copied from this forum:

[lemurman]

All of the cells seem to be formatted as numbers

The first column of data was entered as 1 2 3 4 5 and dragged down through 50. These cells began and ended as numbers.

The cells using the VLOOKUP function are formatted as numbers.

And the cell linked to the drop-down menu is formatted as a number.

I want this sheet to be as simple to use as possible, so doing a find and replace of those characters is not desirable, unless it only has to be down once when I make the sheet.

I guess I could probably link a macro to an event with the drop-down menu to do the find and replace. I haven't had much experience with macros though, and it seems like there should be a simpler solution.

Thanks for the response!

[huwg]

If you click in one of the source range cells does an apostophe appear? That would mean the source range was populated before it was formatted as numbers. If this is the case then running the find & replace on this range should fix things, and would only need doing the once.

[lemurman]

The source range cells do not have apostrophes before them.

When I right-click on the list box and click on form, then on the Data tab, it says that the data source is Bibliography. I don't know what that means, but it doesn't seem right.