Issue 125813 - Set option "search criteria must apply to whole cell" to true, when opening .xls or .xlsx file
Summary: Set option "search criteria must apply to whole cell" to true, when opening ....
Status: REOPENED
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: 4.1.1
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: ms_interoperability
Depends on:
Blocks:
 
Reported: 2014-10-31 12:50 UTC by efa
Modified: 2014-11-01 18:16 UTC (History)
2 users (show)

See Also:
Issue Type: ENHANCEMENT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
.ods that use VLOOKUP() on same radix strings (15.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-31 12:50 UTC, efa 		no flags Details
the same file saved as .xls (33.50 KB, application/vnd.ms-excel)
2014-10-31 16:23 UTC, efa 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description efa 2014-10-31 12:50:25 UTC 
Created attachment 84131 [details]
.ods that use VLOOKUP() on same radix strings

the VLOOKUP() function with 3th parameter at 0 or FALSE, work with not sorted elements, and should return exact match only.
But sometimes get confused by similar element that match too, when should match with 3th parameter to 1 or TRUE or missing

It happen when the radix part of a string is found in another entry, followed by other text.
Attach an example where the bug is shown.

I exported the .ods file as .xls and tested in both AOO and Excel, and so work as expected, return exact match only.
Comment 1 Regina Henschel 2014-10-31 13:00:41 UTC 
It is a feature :).

Goto Tools > Options > OpenOffice Calc > Calculate. Find the option "Search criteria = and <> must apply to whole cells". If that is checked, the complete cell content is used for comparisons, otherwise substrings will match too.
Comment 2 efa 2014-10-31 16:12:18 UTC 
Excel match always the whole cell, so to have compatibility we must keep the option checked. Should be the install default.
Comment 3 efa 2014-10-31 16:16:16 UTC 
this option seems related to the specific file, not a global settings.
Is this confirmed?
Comment 4 efa 2014-10-31 16:23:14 UTC 
Created attachment 84132 [details]
the same file saved as .xls

Attached the same .ods file saved as .xls
Then, opening the .xls file with AOO, the option "Search criteria = and <> must apply to whole cells" is disabled, so the results in D28 is different to that in Excel. This can be very dangerous.
Opening an XLS file, AOO should automatically set this option.
We need to change the bug description adding "opening .xls files"
Comment 5 Regina Henschel 2014-10-31 17:43:16 UTC 
This is a document setting. It is in element <table:calculation-settings> the attribute "table:search-criteria-must-apply-to-whole-cell" in the content.xml in the .ods container.

I agree, that this option should be set to "true", when opening an Excel spreadsheet.

I have changed the Summary and set Issue-Type to enhancement and added keyword "ms-interoperability".

I guess, this can be an "easy-hack".