Description: An identically constructed formula using LOWER() & LEFT() functions on two "identical" sheets produces two different results. A simplified example of the source has been attached. The first sheet is an identical copy of the pertinent sheet in the "master" document whereas the second sheet is structurally replicating the same sheet but with most of the cells removed and the FORMULA() function placed appropriately to demonstrate that the "code" is identical between the two sheets but the results are not. It would surprise me if it has anything to do with my user profile as it's the same profile for both sheets. What I initially thought was a limitation of the conditional formatting facility prompted me to break the CF test into simplified "flags" in the cells adjacent to those requiring CF and it became apparent that the logic was failing. When I proceeded to create an "error file" to submit to LO Bugs it became apparent that the logic is being interpreted differently between the two sheets. The original file is five sheets with some fairly comprehensive defined tables, cross-referencing, URL links to local image files, etc,. These have all been removed from the attached sample and the error persists. Let me know if the original is required for any advanced elimination protocols - NO PERSONAL DATA PRESENT Steps to Reproduce: With the attached file observe that the FUNCTION() commands on LogicOK are identifying the logic comparison between SBee and LogicOK. Observe that the results on SBee are inconsistent with the logic whereas the results on LogicOK are consistent. P10 & AD10 simply repeat B3:B4 which is the procedure for all appropriate columns on SBee The flags are validating:- The words contain the "control" character in the M1:M2 MERGE. Feel free to amend the control character to qualify/quantify the impact OR edit an invalid word into the column The words are not duplicated. Feel free to type a few duplications - you will need to perform an alpha sort if they are not contiguous The words are in the correct columns. Feel free to edit a valid word into the wrong column - or an invalid one for that matter. Under normal condtions the CF would simply colour fill any offending cells. Actual Results: Incorrect logical appraisal for LEFT(P11;1) compared with LOWER(P$10) on the SBee sheet, correct appraisal on LogicOK Expected Results: Correct logical appraisal within both sheets - with particular emphasis on the production version. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.3.7.2 (x64) / LibreOffice Community Build ID: e114eadc50a9ff8d8c8a0567d6da8f454beeb84f CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 183943 [details] Two sheet simplified sample
Letters in SBee.B3:B7, have a space after the letter. LOWER() doesn't eliminate the space, so it has two letters, while LEFT(;1) has one.
(In reply to m.a.riosv from comment #2) > Letters in SBee.B3:B7, have a space after the letter. LOWER() doesn't > eliminate the space, so it has two letters, while LEFT(;1) has one. My apolpgies, The entire array is copied from the web and pasted into a work array before copying to the final location. You beat me by an hour - I was woken by the inspiration that my two examples were not created identically. I'd just figured out that the "source" array responded differently depending upon whether it was copied or typed into place. Unfortunately, I opened your notification after I had figured it out "in situ". Again, My apologies for wasting your time - mine is less important