Toggle between Lists

[dabrot791]

Hi there,

First I just want to thank all you awesome members out there. Through your assistance with others, I have learned a lot about excel.

Now, to the problem.
I am working on a bilingual file and want to display the results dependant upon the language selected.
I have a drop down selection list set up through data validation. When the drop down is selected, it displays according to the language selected, with this formula
=IF(Lang=KOR,ClashIssues,ClashIssuesEng)
However, if I change the language, the preselected item does not change according to the language. I need this to change because I have other cells referencing this cell as a lookup value. What can I do to make this dropdown change?

Any help with this is great!
For reference, I am using Excel 2010.

 

[dabrot791]

Tried to add an image for further explanation but not sure how to do so.

 

[dabrot791]

Please note, due to lack of input, I have posted on the following additional forums.
Dynamic Data Validation Lists
Dynamic Data Validation Lists, Toggling between Languages
http://www.ozgrid.com/forum/showthread.php?t=200518&page=2&p=774416#post774416

Have not come to a successful conclusion yet.

 

[mikerickson]

It looks like you already have three named ranges, ClashIssues, ClashIssuesEng and Lang.
And a named value, KOR.

If you put this in the sheet's code module for the sheet that contains named range Lang, It should do what you want after you've changed the definition of KeyCell to match your situation (i.e. the cell where the validation drop down is)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyCell As Range
    Dim rowIndex As Variant
    
    Set keyCell = Range("I1")
    
    If Not Application.Intersect(Target, ThisWorkbook.Names("Lang").RefersToRange) Is Nothing Then
        With keyCell
            If Evaluate("lang=kor") Then
                rowIndex = Application.Match(.Value, Range("ClashIssuesEng"), 0)
            Else
                rowIndex = Application.Match(.Value, Range("ClashIssues"), 0)
            End If
            If IsNumeric(rowIndex) Then
                .Value = Evaluate(.Validation.Formula1).Cells(rowIndex, 1)
            End If
        End With
    End If
End Sub

 

[dabrot791]

Hey Mike, thanks for the input. Just saw this now, not sure why I missed it before. Here is a link to the solution that I settled on. I will try your solution though as it appears much simpler than mine.

VBA to search for cell and replace value