Dropdown list based on language selection

[tasos8282]

Hi everyone

I made a quick search but didn't find anything similar in the existing threads, so I am posting my question:

I have a sheet where the language can be chosen (German & French) as a dropdown list at the beginning (let's say in A1). In the 50 cells below (A2-A51) there is a second dropdown list with some products, whose names are in the selcted language. When the user selects a product, a corresponding value is returned to the cells B2-B51, based on a VLOOKUP command.

The problem is that if the user makes the product selection in a language (eg in A2) of the product AND THEN changes the language, the selection in A2 remains in the first chosen language and as a result the VLOOKUP command does not work (it is set to function based on the language selection). So the user must re-select all the products in the new language and since it can take some time, I would like to see if it could be avoided.

Is there a way/trick to solve this?

Thanks in advance, I tried to describe it as clear as possible, I hope I made it

 

[dirtychinchilla]

Hi,

My first thought would be to set an on change action for the language cell, so when that cell changes it refreshes all the vlookups. Alternatively, if you are using data validation, you can actually put a formula in the menu where you select which list you'd like to use for your information, so you could do something like =if(A1="German",A2:A50 (range for German products),If(A1="French",B2:B50 (range for French products),"")) which would change the place that the drop down gets it's list from.

I don't know if I've misunderstood your problem, though.

My other thought, if I have misunderstood you, is that either you need something in the workbook that refreshes all the formulae (How do you get Excel to refresh data on sheet from within VBA? - Stack Overflow).

 

[tasos8282]

Thanks a lot for your answer dirtychinchilla.

The problem is that the already selected product does not automatically change after the language changes. Example:

A1: Language=german
A2: Product 1
A3: Product 2
etc.

B2: VLOOKUP(A2, Range, Column, FALSE)
B3: VLOOKUP(A3, Range, Column, FALSE)

A2 and A3 are in German and the Range is dynamic (according to the language and other criteria). The first column of the range is also the List of products (eg for A2 and A3).

If I change the language (French) cells A2 and A3 remain in German although the Range is now in French; the VLOOKUP does not work anymore. The user has to re-select the Products in A2 and A3 in the new language.

I know it is a little complicated and I think I will leave it as it is, the user will either have to select the language at the beginning and not change it anymore or he will just have to do some more effort in case he forgets .

Thanks once more.

 

[dirtychinchilla]

I understand what you're saying now. If I were you I'd create an on change event on the Language cell, A1, something along the lines of

Private Sub Worksheet_Change (ByVal Target As Range)

If Target = Range("A1") Then
Worksheets("Sheet1").Calculate
End If

End Sub

Not sure how well that will actually work though!

 

[tasos8282]

Thanks again I'll give it a try!