International nightmare with boolean inputs

[ecovonrein]

I have a static Excel table with records that contain boolean flags.  That is, a column populated with values True and False.  I wish to assure one of these two values and I hence impose a Data Validation/List on this input.  The allowable list is set to "TRUE;FALSE".

This is all very basic stuff and I never thought anything of it.  Today, all hell breaks loose.  A colleague with a GERMAN installation has opened that database (and related downstream spreadsheets).

First observation:  The Dropdown menu does not suggest the appropriate WAHR;FALSCH but remains at TRUE/FALSE. 

Ok, I get it.  Via Data Validation, I can only constrain pure text strings.  The circumstance that my chosen text strings have a special meaning in my ENGLISH Excels is just that - circumstantial.  The translation from TRUE into 1 takes place AFTER the string input "TRUE" has been validated.

I also get the fix.  I must create a short static spreadsheet range somewhere which contains the values TRUE and FALSE which I shall call TRUEFALSE and can refer to as thus in the Data Validation.  All things being well, Excel should then translate the values in that range into the local language and the Dropdown menu will be good.

(This is amazing of itself, since one might just as well expect this approach to yield a Dropdown menu of 1 and 0.  But I checked - we get the strings.)

But things are a lot wackier still.  In theory, my colleague should only have seen values WAHR and FALSCH in his (translated) view of my spreadsheet.  But no, SOME values come through as TRUE and FALSE, ie Excel will not translate them.

We cannot make out any rhyme or reason.  There is no difference between those English cells and all the German ones.  Anyone ever observed anything like it?  We are talking here about O365, latest versions.

In case the far-reaching significance of this problem is lost, where my English Excel subsequently evaluates =IF(Flag;...;...), his Excel returns #WERT because =WENN("True";...;...) means absolutely nothing to his German Excel.

(Now alerted to this issue, we observe the same problem in Italy and in Spain.  Extraordinary.)

[#2]

@ecovonrein Not sure I follow, but I suggest you try to create your dropdown list based on two cells with formulas =TRUE and =FALSE, rather than texts "TRUE" and "FALSE". The formulas should translate to any language supported by Excel. Can't test it though as I only use the English version. See attached and try.

[#3]

As I wrote, that is my solution for the Data Validation too. I incidentally have the same issue you have. I only have an English Excel. I need to test all this stuff remotely on colleagues' computers.

The biggest problem appears to be though that the translation itself is unreliable. So in theory it could just as well affect your two cells =TRUE and =FALSE.

[#4]

Update: The investigation gets weirder by the minute. I skipped a few details in the post above, one being that the flag was also passed thru some VBA code. I could not understand why the VBA code did not puke. And I just ran a test on a German computer:

Public Function haveFun(flag as Variant) as Variant
  if flag then
    haveFun = "Hello"
  else
    haveFun = "world"
End Function

I can call =haveFun(WAHR) or =haveFun(TRUE) and get "Hello" in both cases.  That is, VBA is indifferent to the input language used.

Do the same thing in native Excel - =if(C5;"Hello";"world") - and we get #WERT! when C5 contains TRUE.

Extraordinary.

I am looking at multiplying the VBA input *1 to assure symmetric behaviour.  TRUE*1 blows up in German Excel.

[#5]

And reflecting on this some more, it is actually the return value from a VBA function that popped up as "TRUE" in the German Excel and prompted a #WERT! in a downstream WENN. There is nothing I can do to turn that into WAHR. The VBA code handles a Boolean throughout. So I might guess that there is a bug in the German Excel which does not translate a Boolean into WAHR/FALSCH but merrily returns TRUE/FALSE.

So, while there appear to be unrelated problems of translation of boolean values, the bigger problem with VBA I cannot solve. My best solution will likely be to steer clear of TRUE/FALSE altogether use only 0 and 1 going forward. Ouch.

[#6]

Reflected on yesterday's disaster overnight and believe to understand the origins of the problem.  Let's bear in mind a few truths about Excel:

• In Excel, it is impossible to tell a difference between a cell containing the word TRUE and the word WAHR.  All cells are Variants (in VBA parlance) and can handle any type of input.
• But upon assignment, a Variant will attempt to classify its value as String/Boolean/Long/Double/...  And it will retain this classification until reassigned. 
• Whether Excel will classify the words TRUE or WAHR as a String or a Boolean depends entirely on the locale of the Excel in which the assignment was first performed.  My English Excel will recognize TRUE as a Boolean / WAHR as a String.  A German Excel the other way around.
• A Boolean is not stored as TRUE|FALSE but as 1|0 and must be rendered whenever displayed.  That is, Excel never translates between locales, it just renders Booleans differently in English and German.
• This complete absence of translation affected my Data Validation.  I now believe that all the entries which fail to translate from English into the local language were originally created in a non-English locale.  To meet my Data Validation, the foreign user could only choose between TRUE and FALSE.  Neither word could however create a Variant/Boolean in their Excel.  So the entry was created as a Variant/String. 
• Whenever a Variant/String is introduced into a Boolean context, the fuzzy type-handling will attempt a type-cast.  In English Excel, the foreign Variant/Strings "TRUE" were hence without consequence to me;  the type-cast from "TRUE" to 1 is possible.  In German Excel, that same type-cast fails.

This analysis explains the odd records that will not translate.  No records are ever translated.  It is just that most records hold a Variant/Boolean that must be rendered (in the local language), while others are Variant/String (that are displayed as stored).

PS:  None of this explains why a VBA function that returns a Variant/Boolean renders to "TRUE" in a German Excel.  Or why a German VBA is happy to type-cast "TRUE" into a Variant/Boolean.