Apache OpenOffice Community Forum

I have been using spreadsheets ever since SuperCalc and have used VLookup many, many times. Just can't find out what's wrong this time. I'm designing an Accounting system. I have 14 Sheets so far. One of them, "Nominal Ledger" includes all the code numbers used and the names of their topics. Another, "Daily transaction record" contains all the entries. I want the Daily transaction record to recover the topic names from Nominal Ledger whenever I type a code into the Daily transaction record (or any other sheet where a code is used). (Spreadsheet attached) The Daily transaction record sheet won't accept my VLookup entry .. "=vlookup(b5;'Nominal Ledger'.A3:b25;2;0)" .. which seems OK syntax to me. Has anyone any ideas?

The formulas are interpreted as text. Possibly the cells were formatted with number format "@".
Click the first cell, hit F2, append a space, enter, copy down.

Accounting spreadsheets pave the road to failure.

Format the cells with the vlookup as Number. Now they are Text

Otherwise nothing wrong

Thanks guys. These numbers need to be text. The codes are, typically, "0065", "0370", "9999". vlookup says you can use text.

DiGro wrote:Format the cells with the vlookup as Number. Now they are Text

Number format is "Default". I think he already changed the format from text to default after text input. However, formatting does not change any values.
=vlookup($B5;'Nominal Ledger'.$a$3:$b$25;2;0) correctly looks up the text in B5 within the text values of A3:A25 and returns the text from B3:B25. The returned value is a text anyway. There is no need to format the formula cells when the result is a text. Formatting never changes any cell value. You can format numbers as text and they always remain numbers. You can format text values as numbers and they always remain text. The number format is just the same as font, underlining or color. It must not affect the cell value by any means. The value is a text unless it is a number and no formatting attribute must change it.
Number format "@" (text) makes sense for literal input of digits or text beginning with a =. It inhibits any interpretation of the entered text.

BINGO!!!
Thanks a bunch guys. There seems to be some limitation I hadn't appreciated with formatting the cells as Number/Text. If I simply change the format of the cells containing the vlookup function to Number/All .. then it works fine!!