Re: [libreoffice-users] how to make formulas permanently case sensitive?

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 
> (4.4.3.2/Mint 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: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted