Use list of language IDs in TEXT formula?

[gino59]

Hi all,
I'm trying to provide the ability for a user to select a language from a drop-down cell and have a different cell value change accordingly. This formula works:
=TEXT(A1,"[$-406]dddd") and turns Thursday into Torsdag (Danish) very nicely.

What I'd like to do is have the [$-406] part of the TEXT function be dynamic. So, if a user chooses French (for example) from the drop-down cell, that would then change the [$-406] to [$-40C] in the formula. (I have a lookup list of Language Name to Language ID).

Thank you!

Gino

 

[admiral100]

Hi,

Try this:

In this example E2:E115 contain the language codes and F2:F115 contain the language names
A12 is the value from the drop-down cell

=TEXT(A1,"[$-"&INDEX(E2:E115,MATCH(A12,F2:F115,0))&"]"&"dddd")

 

[Gerald Higgins]

Try

=TEXT(A1,A2&"dddd")

where A2 contains [$-406] or whatever the language code is.

 

[gino59]

Many thanks, admiral100 and Gerald! Both functions work wonderfully!

Cheers,
Gino