[Solved] VLOOKUP function shown instead of formula result

Discuss the spreadsheet application

[Solved] VLOOKUP function shown instead of formula result

Postby JohnCox » Tue Feb 23, 2021 11:39 am

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?
Attachments
Accounts Crestville.ods
(80.33 KiB) Downloaded 26 times
Last edited by MrProgrammer on Tue Feb 23, 2021 4:13 pm, edited 2 times in total.
Reason: tagged solved.
Open Office 4.1.9 on Win 10
JohnCox
 
Posts: 3
Joined: Tue Feb 23, 2021 11:23 am

Re: VLookup again!

Postby Villeroy » Tue Feb 23, 2021 12:53 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29693
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VLookup again!

Postby DiGro » Tue Feb 23, 2021 12:55 pm

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

Otherwise nothing wrong :D
____________
DiGro

AOO 4.1.9 on Windows 10. Scanned with ZIGGO Safe Online (F-Secure)
User avatar
DiGro
 
Posts: 135
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands

Re: VLookup again!

Postby JohnCox » Tue Feb 23, 2021 1:16 pm

Thanks guys. These numbers need to be text. The codes are, typically, "0065", "0370", "9999". vlookup says you can use text.
Open Office 4.1.9 on Win 10
JohnCox
 
Posts: 3
Joined: Tue Feb 23, 2021 11:23 am

Re: VLookup again!

Postby Villeroy » Tue Feb 23, 2021 1:23 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29693
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VLookup again!

Postby JohnCox » Tue Feb 23, 2021 2:49 pm

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!!
Open Office 4.1.9 on Win 10
JohnCox
 
Posts: 3
Joined: Tue Feb 23, 2021 11:23 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests