Ask Your Question
0

vlookup formulas fail when Calc opened in another language

asked 2018-09-07 03:42:51 +0200

tim gravatar image

I have a spreadsheet that uses linked CSV files to pull data into a worksheet. Formulas on other worksheets then use VLOOKUP to use data from that worksheet. This works fine when the language is set to English. But when I start Libreoffice in German the vlookup seems to fail with "Fehler: Falscher Datentyp" (German for "Error: Wrong data type").

I'm using Ubuntu 18.04.

Specific Example

The attached spreadsheet and CSV file contain data on UK listed companies (all public domain data). The "UK Shares Fundamentals Downloaded Data" worksheet was linked to the CSV file of the same name using these steps: Sheet->Insert Sheet from file..->(select CSV file)->Click OK on Text Import dialogue->Enable 'Link' on Insert Sheet dialogue

The 'Cur Share Price' in the FTSE350 worksheet uses vlookup to get the current share price from that "UK Shares Fundamentals Downloaded Data" worksheet. When the spreadsheet is opened in English everything works, when it's opened with Libreffice in German any formula that uses the value of a 'Cur Share Price' cell shows "Fehler: Falscher Datentyp" (German for "Error: Wrong data type")

Example Spreadsheet File

The Spresheet itself: C:\fakepath\UK Shares Fundamentals.ods

THe CSV file (needs to be a in a folder called 'Data' in the same folder as the spreadsheet, MUST be renamed to .csv (renamed ods just so I could upload here): C:\fakepath\UK Shares Fundamentals Downloaded Data.ods

Screenshots

In English all is fine: C:\fakepath\LO-in-English.jpeg

In German it doesn't work: C:\fakepath\LO-in-German.jpeg

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-09-07 08:20:31 +0200

Supposedly you didn't set explicit language for the second column (right-clicking its header and setting it to US English) in Text Import dialogue. It stays "Default", and its detection as numeric depends on decimal separator used in current locale. Setting it to US English explicitly allows to specify that dots are to be treated decimal separators, regardless of current locale.

image description

edit flag offensive delete link more

Comments

Thanks!! That was it. I now have to go back and link and unlink every CSV file from my spreadsheets, which is a bit of a pain, but at least I know how to have it work now

tim gravatar imagetim ( 2018-09-11 13:29:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-09-07 03:42:51 +0200

Seen: 74 times

Last updated: Sep 07 '18