Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index

At 07:58 15/05/2015 +0100, Mike Scott wrote:
On 14/05/15 10:55, Brian Barker wrote:
> At 20:32 12/05/2015 +0200, Honly Wonly wrote:
>> Am 12.05.2015 um 19:53 schrieb Brian Barker:
>>> At 12:45 12/05/2015 +0200, Honly Wonly wrote:
>>>> I need, of course, formulas to be case sensitive. This can be set in
>>>> the options for calc. The option is not saved permanently and every
>>>> time I open a spreadsheet, I need to set the option again.
>>> I'm surprised by that. If you mean the options at Tools | Options...
>>> | LibreOffice Calc | Calculate | Case sensitive, doesn't this apply
>>> per user and thus stick not only between documents but between
>>> LibreOffice sessions?
>> No, this option is not remembered.
> I've done some research, and the setting is stored in
> registrymodifications.xcu in the profile. So yes: it *will* be saved per
> user, not per document or per system, and will stick across LibreOffice
> sessions. (But perhaps not, as I suggested, if you have a corrupt profile.)

Can I interject a comment and an experiment here?

Firstly, a global per-user setting for case sensitivity would be a recipe for disaster, and for spreadsheets not being properly portable.

But it isn't like that.

Now, checking under Tools|Options....Calculated showed my system ( 17) had the 'case sensitive' option' set. (TBH, I'd never noticed this before, but then I hardly ever use spreadsheets.)

I made a simple spreadsheet: two cells contained 'AAA' (A1) and 'aaa' (A2), and two cells with =IF(A1="AAA", 1, 0) and =IF(A2="AAA", 1, 0) and displaying 1 and 0 as expected. Save this as doc1. Set the Tools case sensitivity to no. The results cells now show 1 and 1 of course; save as doc2, and exit LO.

Now, if I reload doc1 and doc2 simultaneously, showing in 2 windows, both spreadsheets show the same results as before. Furthermore, checking the (expected-to-be-global) Tools option for case sensitivity shows different values when the dialogue is opened from each window.

Again, checking the contents.xml for each file shows an extra entry
<table:calculation-settings table:case-sensitive="false"/>
for the 'case insensitive' file. (Presumably the default is 'true'.)

So as one would hope, the setting is saved per-file.

My suspicion is that any value in registrymodifications.xcu is simply a default for new documents.

(I've communicated privately with Brian before posting the above, and I believe he has some extra germane comment to add.)

Indeed: but basically only to say that everything you say is correct! My apologies to all for the misdirection.

Another couple of bits of evidence that the case-sensitive option is a property of the document:

o If you start a new spreadsheet but close it immediately, you are not challenged to save it or discard it, of course, as it not been modified. But if you start a new spreadsheet and then just change the case-sensitive option, you *are* challenged, confirming that this is indeed a change to the document.

o If you open more than one spreadsheet at the same time which happen to have the same case-sensitive setting, changing the setting affects only the document in which you make the change.

Another contributor suggested that the original enquirer's problem may have been caused by (unwisely) saving spreadsheets in Microsoft Excel format, and it appears that this may well have been so. My experiments suggest that spreadsheets saved with the option set differently are displayed correctly in the Excel Viewer (and therefore presumably in Excel itself) but do not render correctly when reopened - suggesting that it is LibreOffice's input filter that is at fault. Solution: save in .ods format.

But the bottom line is still the same: because the situation is so complicated and fluid, the reliable way to deal with the problem is to build case sensitivity into formulae, testing similarity using the EXACT() function instead of the "=" operator.

Apologies again for the (temporary) distraction.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.