Change values in cells based on dropdown selection

**Gert Van Dessel** · 03-18-2013, 12:49 PM

I have the following problem:

I need to make a report in 2 different languages.
Let's say I put the required texts for the 2 languages in a different colum in a worksheet (e.g. column A for English, column B for Dutch).

Is it possible to use a dropdown in another worksheet to select the correct language (reference to the correct column in the other worksheet)

So, in cell A1 I want to place a dropdown for language selection (EN or DU), in cells A3 to A10 I want to see the text in the correct language based on the other worksheet and corresponding to the language selected in the dropdown.

Is this possible?

Thanks.

**Pauleyb** · 03-18-2013, 02:35 PM

Something like this:
=if(a1="EN",OtherSheet!A1,OtherSheet!B1)

**Gert Van Dessel** · 03-19-2013, 04:18 AM

Is it also an option to work with vlookup and named ranges? If you have a lot of languages that's maybe a better solution?
And if so, how does that work exactly?

Thanks for the input.

**Pauleyb** · 03-19-2013, 09:25 AM

I would recommend INDEX. Assuming a similar format as above. Create your table in OtherSheet, but have row 1 be a header row with the languages (e.g. EN, DU, IT, FR).
A2 would then have the first English word, B2 the Dutch equivalent, etc. A3 would be the second English word,.... For the equation below, let's say your table has 6 languages and 25 words, so it ranges from A1:F26 (including the header row).

On your sheet with the selection (assume it is in A1), then you would use an equation like this in A2 (and then drag across and down):
=INDEX(OtherSheet!$A$1:$F$26,ROW(A2),MATCH($A$1,OtherSheet!$A$1:$F$1))