Description: When there is defined a validity check with cell range and those cells contain invisible characters, especially newline character there are multiple issues. 1) The selection menu has stripped the invisible characters e.g. newline 2) When the value selected to cell, the invisible characters e.g. newline are stripped (visually). How ever vlookup() finds correctly 3) When opening document, then the vlookup() fails, and is fixed after reselection Steps to Reproduce: 1. Fill several cells with normal text and newline characters 2. Set a validity for some cell with the cells as a range above 3. Have at another cell a vlookup() to verify validity of selected value 4. save, close and reopen the document 5. Notice the vlookup() to fail Actual Results: 1. After selection newline characters have no effect of causing a visual new line 2. vlookup() fails after loading document Expected Results: 1. Pulldown menu can have a fixed height cells for value selection 2. When a value is selected, newlines work as expected 3. after loading a document, vlookup() do not fail Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.5 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: en-CA (en_CA.UTF-8); Calc: group
Created attachment 151786 [details] Demonstrating the issues
Please test with newer version. 5. Notice the vlookup() to fail - works for me. I doesn't see fail after reload of document I can confirm: 2) When the value selected to cell, the invisible characters e.g. newline are stripped (visually). Version: 6.4.0.0.alpha0+ Build ID: 2812610f4f39ed5892da08864893c758325d1d39 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; and Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
I tried with Version: 6.3.0.4 Build ID: 057fc023c990d676a43019934386b85b21a9ee99 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: en-CA (en_CA.UTF-8); UI-Language: en-US Calc: threaded The cases 1) Not working as expected (invisible characters stripped) 2) Not working as expected (invisible characters stripped), but VLOOKUP Okay 3) Working as expected
*** Bug 142934 has been marked as a duplicate of this bug. ***
The attachment tested with > Version: 7.1.4.2 / LibreOffice Community > Build ID: 10(Build:2) > CPU threads: 16; OS: Linux 5.8; UI render: default; VCL: gtk3 > Locale: en-GB (en_CA.UTF-8); UI: en-US > Ubuntu package version: 1:7.1.4~rc2-0ubuntu0.20.04.1~lo1 > Calc: threaded Cases 1) The pulldown menu show multiline test, if newline in string. Other invisible characters are shown as a special character, char(7) is shown as 00 07 2) Newlines are still stripped 3) No change in behaviour
I expect there is a real problem here. Perhaps it was fixed with https://gerrit.libreoffice.org/c/core/+/140110. However, in Linux I couldn't reproduce the problem. Newlines seem to exist, and validity reports the correct entry every time I select something in the dropdown. Tested in master, 7.3, and 7.0.
I tested with 7.4 (Linux). Selection seems to be working as expected, only minor complain of pulldown menu showing otherwise invisible char(7) character as unknown character (00/07 block). How ever, if the attached test is saved as the last choice of selection and opening the document again, VLookup test fails and return #N/A
(In reply to Teraslilja from comment #7) > only minor complain of pulldown menu showing otherwise invisible char(7) > character as unknown character (00/07 block). I don't see that (on Linux). Do you have Arial font installed? (I do.) That's the only thing I can think of - it sounds like a font-substitution. > How ever, if the attached test is saved as the last choice of selection and > opening the document again, VLookup test fails and return #N/A I don't see that - even in 7.3. It always opens up with the last A-E result, never N/A.
Recently I have upgraded my Ubuntu to 22.04 LTS and I tested with Calc version 7.3.7.2. Now everything seems to be working correctly. I propose that this ticket is closed.
3) When opening document, then the vlookup() fails, and is fixed after reselection -> is fixed since 6.2 with: https://git.libreoffice.org/core/+/a014a9bcf071229eef93c307c705a4c639635bd5 author Eike Rathke <erack@redhat.com> Wed Jul 18 22:04:09 2018 +0200 committer Eike Rathke <erack@redhat.com> Wed Jul 18 23:21:26 2018 +0200 Do not force all string results to be recalculated if no style set (the related report is: bug 118735) 2) When the value selected to cell, the invisible characters e.g. newline are stripped (visually). -> is fixed since 7.4 with: https://git.libreoffice.org/core/+/44d237c375b7ef9a5a61a2f752bd19b57649ffbd author Andreas Heinisch <andreas.heinisch@yahoo.de> Thu Dec 30 19:57:10 2021 +0100 committer Andreas Heinisch <andreas.heinisch@yahoo.de> Tue Feb 15 07:46:03 2022 +0100 tdf#104902 - Handle embedded newline in Calc's .uno:EnterString 1) The selection menu has stripped the invisible characters e.g. newline -> this still happens, but I think it's fine this way. Also Excel 2016 does the same.