Language in a Pull Down Menu automatically updated

[fred]

Hi, I have a worksheet where users can choose the language type, depending on the users' native language and the workbook covers 8 different languages (default to English).


When a user opens up the file he'll be prompted to click a pull down menu to choose, say, german. And the entire excel workbook would turn into German text. In another cell the user has to click and choose the proper length of time (4 choices: monthly, quarterly, half-yearly and annually) to see the calculation in difference currencies.


Due to current set up, the default has the choice "Monthly" showing up even the german language is picked and subsequent calculation would show up as #N/A UNLESS the user click the period button and the pull down menu would refresh to show: Monatlich, Quartalsweise, Halbjahrlich and Jahrlich (excuse me for not having the correct alpahbet "a" in german). Depending on the choice of time period the calculation would come back correctly and not showing up as #N/A.


Question: is there a way, preferably without macros, to have the time period update once a language other than English is chosen?


80%< of the time the users would want to read the form in "Monthly" (or equivalent local language) format. Those who read the report in quarterly, half yearly or annually know that they have to click the button to refresh the choice. But how about those who just want to read it in monthly/Månedlig/Mensual/Kuukausittain/Månad/Mensal/每月, etc?


Many thanks!

 

[Luke M]

Fred,


To do it, I think you need to use a ListBox from the Forms toolbar rather than a regular data validation drop down. The reason for this, is if you link the listbox to a cell, the output is just a number (say, 3).


Now, assuming somewhere else you have a list of the time periods in the different languages, and somehow via the language selection you can define which list to look at, you should be able to do some sort of INDEX to get the right time period.

E.g.,

=INDEX(AllLists,OutputFromListBox,ColumnSelector_via_Language)


The ListBox could be populated via a dynamic range, so that when a user opens it up, it will show the correct choices. The one downside is that, until the user opens the dropdown, it will still say "month". However, since only the position (aka, the number) is being output, this shouldn't cause a problem in formulas.

 

[fred]

Thanks, Luke M. my concern of list box is that the user move the box around and it'll look ugly and unprofessional. I'll give it a try later today or tomorrow. I'm completely swamped today.


Many thanks!!

 

[SirJB7]

Hi, fred!

If user repositioning is the only concern, why don't you use Worksheet_SelectionChange or ActiveX Listbox_LostFocus events to check and move back it to its original position if moved by user?

Regards!

 

[fred]

Hi SirJB7


the other concern not to use Active X List box is because the worksheet will end up printing on a PDF format and cell pull down would look "cleaner" to the reader without seeing the border of the pulldown button.


The worksheet cannot be protected because the users want to make all kinds of changes. I'm just the creator of the workbook. The users will want the flexiblity to make changes and end up printing worksheet to another group of readers and the worksheet has to "look clean" and professional.

 

[Luke M]

Fred,

What about a formula approach with some sort of error trapping?


What I'm thinking:

=INDEX(TableOfAllChoices,MIN(IF(TableOfAllChoices=TimeChoice,ROW(TableOfAllChoices)-1)),MATCH(LanguageChosen,LanguangeChoices,0))

(This is an array formula)


Idea is to find which choice (1, 2, 3, 4) the user originally had by finding it and returning the relative row. The "-1" part needs to be adjusted to cause first row of TableOfAllChoices to be adjusted to 1 (e.g., if table starts on row 3, change this to -2)

Then, the function figures out which column it should be looking at by using the MATCH function with the LanguageChosen and the LanguangeChoices


Using slightly different words, here's an example:

[pre]

Fruit	Vegetable	Meat
apple	corn	        steak
orange	brocolli	chicken
pear	peas	        pork

Category	Meat
Choice	        orange
Adj. Choice	chicken

[/pre]
Where Fuirt, Vegetable, and Meat are the "language choices", and the cells from apple to pork would be the "TableOfAllChoices". User inputs into the Category and Choice, but the formula for Adj. Choice makes sure the correct row for chosen category is used.

Does that help, or clear as mud?

 

[Mike86]

There's always the linked list concept. Your first list would be languages. When the language is selected, you'd bring up the time list using the appropriate language.


http://helpdeskgeek.com/office-tips/create-multiple-linked-dropdown-lists-in-excel/


You could probably drive the workbook's table and graph titles from the User's language selection easily enough without macros

 

[dan_l]

fred:


Would you share the broad details about this project? 8 languages? Jeeze.

 

[fred]

Hi dan_I, sorry I was out for quite awhile. It was a sales quote form in Excel format. The sales quote form would be in English(U.S.), German, Australian, European Spanish(not S. American spanish, i didn't know there is a difference), Norwegian, Italian, Brazilian portugese, Finnish, Swedish, French, Japanese, Indian, chinese etc.


All the text in the form is formula driven so that a pulldown menu would show up and users, depending on their local office, can choose the desired language. Together with a currency choice, the user can also display only product price in local currency. Basically, every cells other than the "Quantity" cells are linked cells or formula driven cells.


The form would look neat because a german customer can read the sales quote in his language and he can pay in Euro instead of US dollars.


It was pretty fun working on this project. Every word/phrase and its context has to be explained to the translator so that they can do a proper tranlastion that is acceptable to the legal department in the US and its local rules/regulations/customs. I'd also prepare a US version of the form and educate the translator what the cells title mean. because of legal context and stuff you just can't use Google translate or Babel fish to do the job.


The most impressive part of this form is that once you have picked a currency Excel would know if this currency accept 1 decimal point, 2 decimal points, no decimal point (like Japanese yen), -1 decimal point (indonesian rupiah/chilean peso) or -2 decimal point (colombian peso). And all volume discount calculation would take into such consideration the periodic payment would tie out smoothly. That's total amount = sum of all payments.