User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.106 Safari/537.36 Build Identifier: f99d75f39f1c57ebdd7ffc5f42867c12031db97a I have an calc sheet originally created in OO-4 but it kept crashing, so I switched to LO The problem is with some VLOOKUP functions, it produces a large number in stead of text, and if I change anything to the formula it produces the correct response and when I change it back also, but as soon as I reopen the document it give the big number again. example: =VLOOKUP($A48;$'All Orders'.A$1:I$10000;4) removing for instance a $ will produce the correct value Also when I do a pull down to fix all cells it goes back to the wrong output, but when I first do 2 and than a pull down it works basically it makes Calc useless unless this is fixed. Reproducible: Always Steps to Reproduce: 1.open document 2. fix the output 3. save the document 4. open the document Actual Results: 1273328996 Expected Results: Belgium Reset User Profile?Yes
Hi Martin, thanks for reporting. Without a sample file it's not easy to know what is happening. You are not using the fourth parameter so I guess the $'All Orders'.A$1:I$10000 is sorted. Please could you attach a minimal sample file where to reproduce the error, and report what is your LibreOffice version (Menu/Help/About LibreOffice - you can select and copy the text)
Created attachment 126916 [details] File with the unwanted behaviour To 'solve' the problem delete row 102 from tab Feb and do a recalculation by pulling the first line down.
Created attachment 126917 [details] faulty VLOOKUP behaviour aswell When trying to recreate this issue I noticed that there are more things wrong with the VLOOKUP function as you can see in this simple file
More than 100 rows seem to be causing issues for sure, also parsing text looks to be a problem.
(In reply to Martijn Schrama from comment #2) > Created attachment 126916 [details] > File with the unwanted behaviour > > To 'solve' the problem delete row 102 from tab Feb and do a recalculation by > pulling the first line down. Win10x64 Version 5.1.5.2 Opening the file I see the wrong values in Feb column D, but after a hard recalc [Ctrl+Shift+F9] everything looks fine, even after save and open.
(In reply to Martijn Schrama from comment #3) > Created attachment 126917 [details] > faulty VLOOKUP behaviour aswell > > When trying to recreate this issue I noticed that there are more things > wrong with the VLOOKUP function as you can see in this simple file The problem here it's you are not using the fourth parameter for VLOOKUP and the searched array it's not sorted in ascending order. As text 'Name 101' is smaller than 'Name 3' https://help.libreoffice.org/Calc/Spreadsheet_Functions#VLOOKUP So works for me adding to VLOOKUP with zero as fourth parameters.
(In reply to m.a.riosv from comment #5) > (In reply to Martijn Schrama from comment #2) > > Created attachment 126916 [details] > > File with the unwanted behaviour > > > > To 'solve' the problem delete row 102 from tab Feb and do a recalculation by > > pulling the first line down. > > Win10x64 Version 5.1.5.2 > Opening the file I see the wrong values in Feb column D, but after a hard > recalc [Ctrl+Shift+F9] everything looks fine, even after save and open. For me this does not do anything Win7x64 Version: 5.1.4.2
Please try resetting the user profile, sometimes solves strange issues. https://wiki.documentfoundation.org/UserProfile Usually it's enough renaming/deleting the file "user/registrymodifications.xcu", it affects all the options in Menu/Tools/Options, and the files "user/basic/dialog.xlc" and "scrip.xlc" are overwritten, additionally custom colors in "user/config/standard.soc" are lost.
(In reply to m.a.riosv from comment #8) > Please try resetting the user profile, sometimes solves strange issues. > https://wiki.documentfoundation.org/UserProfile > Usually it's enough renaming/deleting the file > "user/registrymodifications.xcu", it affects all the options in > Menu/Tools/Options, and the files "user/basic/dialog.xlc" and "scrip.xlc" > are overwritten, additionally custom colors in "user/config/standard.soc" > are lost. Appararently updating to the latest version seems to have done the trick