International data validation

mathetesSorry, John, there really isn't any magic to his.  Sure, my actual selection has nothing to do with meat, but it might as well.  It really does not matter.  Anyway, in the absence of any feedback from fellow travellers, I thought of a VBA solution to the problem.   I attach it here in case it helps out anybody else.  Plenty of explanations are in comments.

Option Explicit

' Multi-lingual programming in Excel is horrible in general, but multi-lingual dropdowns (aka
' Data Validation) are a particular challenge.

' The validation list is easy enough to create.  We use a simple two-column range somewhere in
' the spreadsheet:

'     xxxx yyyy
'     xxxx yyyy
'     xxxx yyyy

' This range is the mapChoices below.  We can then proceed to install

'   =INDEX(mapChoices,0,1)

' as the list of valid choices for Excel's Data Validation.  In the final step of a single-
' language spreadsheet, we would then obtain yyyy for a given (valid) xxxx by simple

'   =VLOOKUP("xxxx",mapChoises,2,FALSE)

' Easy enough.  But all hell breaks loose when xxxx in mapChoices is itself the result of a
' lookup in some dictionary.  That is, the Data Validation is localized.

' In this case, when an English user saves  the spreadsheet with his (valid) input of "Pork",
' this input must translate into "Schweinefleisch" for a German user of that same spreadsheet
' because "Pork" is no longer a valid choice among the German translations of mapChoices.

' Use validItem in lieu of VLOOKUP to achieve this functionality.  The logic behind validItem
' is that the current code yyyy MUST BE a valid choice - from whichever xxxx it was oringally
' obtained.  If that is so, then  at any time,  the correct current xxxx is simply that which
' corresponds to the given yyyy (ie a reverse VLOOKUP).

' Now, there are countless mechanical challenges within this trivial logic:
'   1 Excel will not reveal the value of thisCell and reward any access attempt with a "ciruclar
'     reference" error.  That is, I cannot find yyyy.  To work around this irriating problem, I
'     must duplicate yyyy somewhere in the spreadsheet.  The inputs dRow and dCol allow you to
'     designate the caching cell by relative reference to the current cell.  A call like

'       =validItem(E13,MapCHOICES,0,1)

'     will cache yyyy one cell to the right of the call.  BE CAREFUL:  DO NOT place the cache
'     anywhere where its update might prompt an unnecessary calculation !!

'   2 Excel will not allow a FUNCTION to manipulate any cell in the Worksheet.  Therefore, the
'     duplication of yyyy must be delegated to a SUB.

'   3 The same applies to the first input into validItem - the range reference to xxxx.  validItem
'     cannot alter that value (xxxx).

'   To make 2 + 3 work, I record instructions into two dictionaries.  Any Worksheet that wants to
'   use validItem must include the follwoing event code into its VBA event module:

' Private Sub Worksheet_Calculate()
'     sweepDynamicDataValidations Me.Name
' End Sub

'   The macro sweepDynamicDataValidations will then process any instructions, updating cache values
'   and dropdown inputs where required.

' NOTES:
' Requires a reference to the Microsoft Scripting Runtime
' NEVER reference the cache in any calculation!  Your downstream programming must exclusively
'   reference validItem.
' When you use validItem in a Workbook set to Manual Calculation, you will sometimes find that
' after F9 finishes a calculation, the Calculate indicator remains on.  That is because
' sweepDynamicDataValidations has leapt into action and altered some cells.  A second
' F9 should then eventually extinguish the Calculate indicator.

Dim ddvDict As New Dictionary
Dim cacheDict As New Dictionary

Public Sub sweepDynamicDataValidations(sn As String)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(sn)
    
    Dim d As Dictionary
    Dim k As Variant, v As Variant
        
    If ddvDict.Exists(sn) Then
        Set d = ddvDict(sn)
'       Processing updates of all input cells after a mapChoices change
        For Each k In d.keys
            v = d(k)
            d.Remove k
            ws.Range(k) = v
        Next
    End If
    
    If cacheDict.Exists(sn) Then
        Set d = cacheDict(sn)
'       Processing updates of the cache cells where a user selection was changed
        For Each k In d.keys
            v = d(k)
            d.Remove k
            ws.Range(k) = v
        Next
    End If
End Sub

Public Function validItem(aChoice As Range, mapChoices As Range, dRow As Long, dCol As Long) As Variant
    Dim c As Range
    Set c = Application.ThisCell
    
    Dim sn As String
    sn = c.Parent.Name
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(sn)
    
    Dim cacheAddr As String
    cacheAddr = ws.Cells(c.Row + dRow, c.Column + dCol).Address
    
    If cacheAddr = c.Address Then
        validItem = [#VALUE!]
        Exit Function
    End If
    
    Dim cache As Range
    Set cache = ws.Range(cacheAddr)
        
    Dim nChoices As Long
    nChoices = mapChoices.Rows.Count
    
'   Let's perform a quick VLOOKUP(aChoice,mapChoises,2,FALSE)
    
    Dim iChoice As Long
    For iChoice = 1 To nChoices
        If aChoice.Value = mapChoices(iChoice, 1).Value Then
            validItem = mapChoices(iChoice, 2)
            
'           Let's do a wee optimization here: Instruct an update of the cache only
'           when the user selection was changed. Doesn't just reduce CPU cycle, it
'           also eliminates most repeat calculations.

            If cache.Value <> validItem Then
                If Not cacheDict.Exists(sn) Then Set cacheDict(sn) = New Dictionary
                
'               Creating an instruction for sweepDynamicDataValidations to update the
'               cell at cacheAddr with the value of validItem.
                cacheDict(sn)(cacheAddr) = validItem
            End If
            
            Exit Function
        End If
    Next
    
    validItem = cache.Value

'   We only find here if aChoice does not feature in mapChoices. This cannot be - the
'   whole point of Data Validation is to make this impossible - UNLESS we were called
'   following a change to mapChoices.  Now, the whole point of mapChoices is to map a
'   user-friendly input (in column 1) into a code-friendly token (in column 2).  That
'   is, the token in cache (from a previous  valid selection  among different choices
'   in column 1) must allow us to  establish the current user-friendly input for this
'   selection.

    For iChoice = 1 To nChoices
        If mapChoices(iChoice, 2).Value = cache.Value Then

'           Hurrah - we found the code.  mapChoices(iChoice,1) is hence the dropdown
'           label that currently corresponds to the user's selection. Let's schedule
'           an update by sweepDynamicDataValidations of aChoice with that new label.

            If Not ddvDict.Exists(sn) Then Set ddvDict(sn) = New Dictionary
            ddvDict(sn)(aChoice.Address) = mapChoices(iChoice, 1).Value
            
            Exit Function
        End If
    Next
    
'   Well, that's odd...
    validItem = [#VALUE!]
End Function

I also attach a little demo including this code.  It works well-enough and isn't heavy.  I will try it in production.  See how it goes.  Thanks.

PS:  There is a mild hazard in Manual Calculation.  It is possible for the user to change his selection in English, NOT calculate and then change the language selection to German.  In that bizarre case, sweepDynamicDataValidations will reset the user input to the German word for the previous selection.