how to make formulas permanently case sensitive?

classic Classic list List threaded Threaded
20 messages Options
hw hw
Reply | Threaded
Open this post in threaded view
|

how to make formulas permanently case sensitive?


Hi,

I need, of course, formulars 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.

How can I save this option permanently so I don't need to set it over
and over again?

--
To unsubscribe e-mail to: [hidden email]
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
hw hw
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?



Am 12.05.2015 um 12:45 schrieb hw:

>
> Hi,
>
> I need, of course, formulars 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.
>
> How can I save this option permanently so I don't need to set it over
> and over again?
>

To clarify:  I need the string comparisons of IF() to be case sensitive:
  'if(a1 = "x"; ...) is, of course, different from 'if(a1 = "X"; ...)'.


Why would anyone make them not case sensitive unless explicitly
specified, and how do I specify case sensitiveness per formula?

--
To unsubscribe e-mail to: [hidden email]
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
gcatlast gcatlast
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

To make a formula that is case sensitive, you should be able to use the string functions (I think) to convert to a known case before performing the comparison, e.g. if(UPPER(A1) = "X";...)
/Gary
       From: hw <[hidden email]>
 To: [hidden email]
 Sent: Tuesday, 12 May 2015, 11:55
 Subject: Re: [libreoffice-users] how to make formulas permanently case sensitive?
   


Am 12.05.2015 um 12:45 schrieb hw:

>
> Hi,
>
> I need, of course, formulars 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.
>
> How can I save this option permanently so I don't need to set it over
> and over again?
>

To clarify:  I need the string comparisons of IF() to be case sensitive:
  'if(a1 = "x"; ...) is, of course, different from 'if(a1 = "X"; ...)'.


Why would anyone make them not case sensitive unless explicitly
specified, and how do I specify case sensitiveness per formula?



--
To unsubscribe e-mail to: [hidden email]
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


 
--
To unsubscribe e-mail to: [hidden email]
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
gcatlast gcatlast
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

Sorry, that's to make a formula NOT case sensitive./G.
       From: Gary Collins <[hidden email]>
 To: hw <[hidden email]>; "[hidden email]" <[hidden email]>
 Sent: Tuesday, 12 May 2015, 12:16
 Subject: Re: [libreoffice-users] how to make formulas permanently case sensitive?
   
To make a formula that is case sensitive, you should be able to use the string functions (I think) to convert to a known case before performing the comparison, e.g. if(UPPER(A1) = "X";...)
/Gary
       From: hw <[hidden email]>
 To: [hidden email]
 Sent: Tuesday, 12 May 2015, 11:55
 Subject: Re: [libreoffice-users] how to make formulas permanently case sensitive?
 


Am 12.05.2015 um 12:45 schrieb hw:

>
> Hi,
>
> I need, of course, formulars 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.
>
> How can I save this option permanently so I don't need to set it over
> and over again?
>

To clarify:  I need the string comparisons of IF() to be case sensitive:
  'if(a1 = "x"; ...) is, of course, different from 'if(a1 = "X"; ...)'.


Why would anyone make them not case sensitive unless explicitly
specified, and how do I specify case sensitiveness per formula?



--
To unsubscribe e-mail to: [hidden email]
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




 
--
To unsubscribe e-mail to: [hidden email]
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


 
--
To unsubscribe e-mail to: [hidden email]
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
gcatlast gcatlast
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

In reply to this post by gcatlast

     They seem to be case sensitive by default. I've just tried if (B1 = "x";5;3)and it returns 5 when B1 = "x" and 3 when B1 = "X". 
To make a formula that is NOT case sensitive, you should be able to use the string functions (I think) to convert to a known case before performing the comparison, e.g. if(UPPER(A1) = "X";...)
/Gary
      
 
--
To unsubscribe e-mail to: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

In reply to this post by hw
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?

>How can I save this option permanently so I don't need to set it
>over and over again?

If it doesn't stick - and if this option is, as I imagine, saved in
the user profile - you might want to wonder if your user profile has
become corrupt. If you close LibreOffice and delete or rename the
profile, LibreOffice will create a new, clean one next time you start
it. If you then set the option again, does it now stick?

At 12:55 12/05/2015 +0200, Honly Wonly wrote:
>To clarify: I need the string comparisons of IF() to be case sensitive:
>'if(a1 = "x"; ...)' is, of course, different from 'if(a1 = "X"; ...)'.
>
>Why would anyone make them not case sensitive unless explicitly specified, ...

I suspect those who prefer the other option would ask the opposite
question! If you are looking for references to dogs, you might want
to find "Dogs are friendly pets" as well as "My dog's got no nose".

>... and how do I specify case sensitiveness per formula?

If the option is, as I suspect, per user, your spreadsheet will go
wrong if you need to change the option for another document, if a
different user handles it on the same computer, or if you exchange it
with someone using a different computer. So you are quite right to
identify that building the requirement into your formulae and not
relying on options is the reliable way to go. And it's quite easy:
the EXACT() function comes to your aid. Instead of using
=IF(A1="X";...)
use
=IF(EXACT(A1;"X");...)
This will match "X" but not "x" irrespective of the option setting.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

hw hw
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?



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.

>> How can I save this option permanently so I don't need to set it over
>> and over again?
>
> If it doesn't stick - and if this option is, as I imagine, saved in the
> user profile - you might want to wonder if your user profile has become
> corrupt. If you close LibreOffice and delete or rename the profile,
> LibreOffice will create a new, clean one next time you start it. If you
> then set the option again, does it now stick?

I have seen this on different computers, with both the Linux and the
Windoze version of LO.

>
> At 12:55 12/05/2015 +0200, Honly Wonly wrote:
>> To clarify: I need the string comparisons of IF() to be case sensitive:
>> 'if(a1 = "x"; ...)' is, of course, different from 'if(a1 = "X"; ...)'.
>>
>> Why would anyone make them not case sensitive unless explicitly
>> specified, ...
>
> I suspect those who prefer the other option would ask the opposite
> question!

Things are usually case sensitive, unless you specify otherwise.  It is
what I expect.

> If you are looking for references to dogs, you might want to
> find "Dogs are friendly pets" as well as "My dog's got no nose".

I don't like dogs ...

>> ... and how do I specify case sensitiveness per formula?
>
> If the option is, as I suspect, per user, your spreadsheet will go wrong
> if you need to change the option for another document, if a different
> user handles it on the same computer, or if you exchange it with someone
> using a different computer.

I haven't tried with different users.

> So you are quite right to identify that
> building the requirement into your formulae and not relying on options
> is the reliable way to go. And it's quite easy: the EXACT() function
> comes to your aid. Instead of using
> =IF(A1="X";...)
> use
> =IF(EXACT(A1;"X");...)
> This will match "X" but not "x" irrespective of the option setting.

Thank you, maybe that works; I'll try it tomorrow.

--
To unsubscribe e-mail to: [hidden email]
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

hw hw
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

In reply to this post by gcatlast


Am 12.05.2015 um 13:16 schrieb Gary Collins:
> To make a formula that is case sensitive, you should be able to use the string functions (I think) to convert to a known case before performing the comparison, e.g. if(UPPER(A1) = "X";...)

That would make the formula case insensitive.

--
To unsubscribe e-mail to: [hidden email]
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
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

In reply to this post by hw
Am 12.05.2015 um 12:55 schrieb hw:

>
>
> Am 12.05.2015 um 12:45 schrieb hw:
>>
>> Hi,
>>
>> I need, of course, formulars 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.
>>
>> How can I save this option permanently so I don't need to set it over
>> and over again?
>>
>
> To clarify:  I need the string comparisons of IF() to be case sensitive:
>  'if(a1 = "x"; ...) is, of course, different from 'if(a1 = "X"; ...)'.
>
>
> Why would anyone make them not case sensitive unless explicitly
> specified, and how do I specify case sensitiveness per formula?
>


All spreadsheets had been case insensitive ever since.
Use the EXACT function for case sensitive comparison.

The calculation options do not apply to Excel files which is why you
have to reset this option every time you load the file. The Excel file
format has no place where this setting can be stored.


--
To unsubscribe e-mail to: [hidden email]
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
David Lynch David Lynch
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

On 12/05/2015 21:39, Andreas Säger wrote:

> Am 12.05.2015 um 12:55 schrieb hw:
>>
>> Am 12.05.2015 um 12:45 schrieb hw:
>>> Hi,
>>>
>>> I need, of course, formulars 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.
>>>
>>> How can I save this option permanently so I don't need to set it over
>>> and over again?
>>>
>> To clarify:  I need the string comparisons of IF() to be case sensitive:
>>   'if(a1 = "x"; ...) is, of course, different from 'if(a1 = "X"; ...)'.
>>
>>
>> Why would anyone make them not case sensitive unless explicitly
>> specified, and how do I specify case sensitiveness per formula?
>>
>
> All spreadsheets had been case insensitive ever since.
> Use the EXACT function for case sensitive comparison.
>
> The calculation options do not apply to Excel files which is why you
> have to reset this option every time you load the file. The Excel file
> format has no place where this setting can be stored.
>
"All spreadsheets had been case insensitive ever since. " - I don't
understand this.

The OpenFormula specification says that comparisons are case sensitive
unless HOST-CASE-SENSITIVEisfalse.

 >>>


      6.4.7Infix Operator "="

Summary:Report if two values are equal

Syntax:ScalarLeft =ScalarRight

Returns:Logical

Constraints:None

Semantics:Returns TRUE if two values are equal. If the values differ in
type, return FALSE. If the values are both Number, return TRUE if they
are considered equal, else return FALSE. If they are both Text, return
TRUE if the two values match, else return FALSE. For Text values, if the
calculation settingHOST-CASE-SENSITIVEisfalse, text is compared but
characters differencing only in case are considered equal. If they are
both Logicals, return TRUE if they are identical, else return FALSE.
Error valuescannotbe compared to a constant Error value to determine if
that is the same Error value.


 >>>

--
To unsubscribe e-mail to: [hidden email]
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
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

Am 13.05.2015 um 12:27 schrieb David Lynch:

> "All spreadsheets had been case insensitive ever since. " - I don't
> understand this.
>
> The OpenFormula specification says that comparisons are case sensitive
> unless HOST-CASE-SENSITIVEisfalse.
>

I've never seen any spreadsheet program (Lotus 1-2-3, Excel, Gnumeric,
Calc) with case sensitive string comparison.
But all of them have an EXACT function to enforce case sensitivity.



--
To unsubscribe e-mail to: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

In reply to this post by hw
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.)

>>>How can I save this option permanently so I don't need to set it
>>>over and over again?
>>
>>If it doesn't stick - and if this option is, as I imagine, saved in
>>the user profile - you might want to wonder if your user profile
>>has become corrupt. If you close LibreOffice and delete or rename
>>the profile, LibreOffice will create a new, clean one next time you
>>start it. If you then set the option again, does it now stick?
>
>I have seen this on different computers, with both the Linux and the
>[Windows] version of LO.

If you use different systems, the setting would need to be made in
your profile separately on each, of course.

>>At 12:55 12/05/2015 +0200, Honly Wonly wrote:
>>>To clarify: I need the string comparisons of IF() to be case sensitive:
>>>'if(a1 = "x"; ...)' is, of course, different from 'if(a1 = "X"; ...)'.
>>>
>>>Why would anyone make them not case sensitive unless explicitly
>>>specified, ...
>>
>>I suspect those who prefer the other option would ask the opposite question!
>
>Things are usually case sensitive, unless you specify otherwise. It
>is what I expect.

I'm sorry you missed my point. I understood what you wanted. Anyone
reading your enquiry will have understood what you wanted. My point
was that others might prefer the existing arrangement. I think others
should be given consideration.

>>If you are looking for references to dogs, you might want to find
>>"Dogs are friendly pets" as well as "My dog's got no nose".
>
>I don't like dogs ...

Did you imagine I was writing for your benefit alone? There are
plenty of dog-lovers on the list.

>>>... and how do I specify case sensitiveness per formula?
>>
>>If the option is, as I suspect, per user, your spreadsheet will go
>>wrong if you need to change the option for another document, if a
>>different user handles it on the same computer, or if you exchange
>>it with someone using a different computer.
>
>I haven't tried with different users.

Actually, it appears you have. You say you have experience of this
matter on different operating systems. Even though you are the same
person on each, you will be separate users and have separate profiles
on the different systems. It is precisely because you may want to
move documents in this way that it is far better to control what
happens in your formulae and not to rely on option settings.

>>So you are quite right to identify that building the requirement
>>into your formulae and not relying on options is the reliable way
>>to go. And it's quite easy: the EXACT() function comes to your aid.
>>Instead of using
>>=IF(A1="X";...)
>>use
>>=IF(EXACT(A1;"X");...)
>>This will match "X" but not "x" irrespective of the option setting.
>
>Thank you, maybe that works; I'll try it tomorrow.

Oh, it does work for others. I'm not sure how your use if it
downgrades this to "maybe".

Brian Barker  


--
To unsubscribe e-mail to: [hidden email]
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

Mike Scott Mike Scott
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

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.)


--
Mike Scott
Harlow, Essex, England

--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: 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: [hidden email]
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

hw hw
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

In reply to this post by Mike Scott


Am 15.05.2015 um 08:58 schrieb Mike Scott:
 >

> 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.

Thank you for testing this!

I wouldn't hope that because this setting appears among others which are
(or seem to be) global (or defaults or the desired settings, however you
might call it), with no indication that this particular setting is per
spreadsheet.

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

How are we supposed to know which settings made at Tools->Options are
global and which aren't?  I would expect all of them to be global and to
be remembered between sessions like every other software does in such
context.

--
To unsubscribe e-mail to: [hidden email]
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

hw hw
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

In reply to this post by Brian Barker


Am 15.05.2015 um 10:03 schrieb Brian Barker:
>
> 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.

The spreadsheets are created with a perl module (Excel::Writer::XLSX)
because there doesn't seem to be an equivalent module to create ODS
files.  Saving them as ODS is not an option because it's annoying having
to switch between different formats and to create various copies of the
files all the time.

Even saving them as CSV to import them is a step that needs to be
removed from the workflow.

> 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.

I don't understand why or how this is complicated.  When I make a
general/global setting amongst others, I simply expect this setting to
be in effect regardless which document I open.

If this setting cannot be made general/global, then it needs to be clear
that it is per document --- not considering that some ppl might
nonetheless wish that a global/general setting for this exists.  And if
a setting which is per document overrides a general/global setting, I
should optionally receive a warning.

--
To unsubscribe e-mail to: [hidden email]
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

Mike Scott Mike Scott
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

On 18/05/15 16:50, hw wrote:
......
> The spreadsheets are created with a perl module (Excel::Writer::XLSX)
> because there doesn't seem to be an equivalent module to create ODS
> files.  .....

That's possibly because you don't really need one. Or at least, it's
easy enough to create the necessary xml by hand. Easiest way is to
create a document like the one you want, and examine its structure, then
make your code write something similar. Some quick work with perl's
zip-file modules, and you're done. I've a number of programs that do
just this.

>........
> I don't understand why or how this is complicated.  When I make a
> general/global setting amongst others, I simply expect this setting to
> be in effect regardless which document I open.
>
> If this setting cannot be made general/global, then it needs to be clear
> that it is per document --- not considering that some ppl might
> nonetheless wish that a global/general setting for this exists.  And if
> a setting which is per document overrides a general/global setting, I
> should optionally receive a warning.
>

I sympathize. IMO the GUI is badly designed (iow broken), and the case
sensitivity might better appear somewhere in the File|Properties
dialogue. I'll not hold my breath though.


--
Mike Scott
Harlow, Essex, England

--
To unsubscribe e-mail to: [hidden email]
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

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

In reply to this post by hw
Am 18.05.2015 um 17:50 schrieb hw:
>
>

> I don't understand why or how this is complicated.  When I make a
> general/global setting amongst others, I simply expect this setting to
> be in effect regardless which document I open.
>

All of Calc's general settings are stored globally.
All calculation settings are stored in the ODF spreadsheet. Excel does
not have any such setting so neither your Perl script nor MS Excel can
store any such setting anywhere when using Excel file formats.
The default setting for foreign file formats is case-insensitive since
this is what spreadsheets use to be since 30 years.
All Excel support forums recommend the EXACT function for case sensitive
string comparison. It is very easy to write EXACT(A1,A2) instead of
A1=A2 which works with all supported spreadsheet formats.



--
To unsubscribe e-mail to: [hidden email]
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

Mike Scott Mike Scott
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

On 18/05/15 20:27, Andreas Säger wrote:

> Am 18.05.2015 um 17:50 schrieb hw:
>>
>>
>
>> I don't understand why or how this is complicated.  When I make a
>> general/global setting amongst others, I simply expect this setting to
>> be in effect regardless which document I open.
>>
>
> All of Calc's general settings are stored globally.
> All calculation settings are stored in the ODF spreadsheet. Excel does

It's not so much where they're stored, as the access route. This
particular setting - case sensitivity - is stored in the document, but
accessed in a dialogue that one might expect to be concerned with global
settings.

There seems to be a similar issue with "precision as shown" on the same
settings page. Not that I, personally, would ever, ever countenance that
as an available option in the first place.



--
Mike Scott
Harlow, Essex, England

--
To unsubscribe e-mail to: [hidden email]
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

Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: how to make formulas permanently case sensitive?

Am 19.05.2015 um 09:35 schrieb Mike Scott:

>
> It's not so much where they're stored, as the access route. This
> particular setting - case sensitivity - is stored in the document, but
> accessed in a dialogue that one might expect to be concerned with global
> settings.
>
> There seems to be a similar issue with "precision as shown" on the same
> settings page. Not that I, personally, would ever, ever countenance that
> as an available option in the first place.
>
>
>

For the Excel documents discussed here there is no way to store anything
at all because Excel does not know any such setting. The issue with
"precision as shown" is the same issue as with any other setting on that
options page. All the calculation settings are document specific to all
ODF spreadsheets. The options dialog needs some way to indicate which
settings are stored in the document and which ones are stored in the
user profile.
The solution to this topic for all kinds of spreadsheet documents is the
EXACT function.


--
To unsubscribe e-mail to: [hidden email]
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