Dynamic Data Validation 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=x,lista,listb)
However, if I change the language after selecting an item from the dropdown, 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. I also need this input cell to be the active cell... in otherwords, I can't use a dummy cell somewhere else and reference to it. What can I do to make this dropdown change?

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

[FlameRetired]

Hi DaBrot791,

I think we are going to need you to upload an Excel file (not a pic or screenshot) so we can see this in context.

Be sure to desensitize the data.

If you are not familiar with how to do this:

• To attach a file to your post,
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• The file name will appear at the bottom of your reply.

[DaBrot791]

Hi Dave,

I have created a quick file to show what I want to do, unfortunately it seems the company is blocking me from uploading. I will have to try it again when I get home tonight.

[FlameRetired]

Looking forward to it.

[DaBrot791]

Please refer to the attachment below.

[FlameRetired]

DaBrot791,

From your notes in the attachment.

<- After Selection, if Language is changed in Log tab, this should change accordingly, as should subissue and subsubissue

I do not find a Log tab.

I am assuming Log tab is Set-up tab?

Or is there a tab missing?

[FlameRetired]

Also what do you want it (Drop-down in Report C2) to change to?

<- After Selection, if Language is changed in Log tab, this should change accordingly, as should subissue and subsubissue

There are 7 possibilities each in SampleA and SampleB (the Data Validation lists for C2).


Edit The same holds true for

<-Currently if language changes, Issue does not change and subissue and subsubissue become blank

[DaBrot791]

Dave,

You are correct in your assumption that the Log tab is the Set-up tab. There are no missing tabs.
In the sample, currently "5" is selected as the issue. 5 is part of Sample List B, the "Other" language.
If the "Other" Langauge is selected in the Set up tab, then C3 should display the corresponding sub-issue to C2 from Sublist B and C4 should display the corresponding sub-issue to C@ from SubSubB.
This is all easily handled upon first entry of the "Issue".
The problem comes when I change the Language in Set-up.
Upon Changing the language, the Issue in C2 should change to the Sample List A item that corresponds to the previously input Sample List B item.
C3 and C4 are currently built to easily follow suit. For now however, I can't get the Sample List B Item to change to Sample List A item, thus, when language changes, C3 and C4 return a blank.

Simply put, previously entered inputs should change according to the language selected.
I hope that is clear enough. Please let me know if not.

[FlameRetired]

No. It's not clear.

Switching the lists part is clear. But which item from the changed list do you want upon changing Lang? There are 7 possibilities each for SampleA and SampleB.

Regarding the switched lists upon changing Lang it sounds like you need to explore Dependent Drop Down Lists for more advanced tips as it appears your DV formula in C2 already changes the dropdown list in C2. Here is a link with several articles on the topic.

http://www.contextures.com/xlDataVal02.html

[DaBrot791]

I appreciate the recommendation of dependent drop downs, but unfortunately that option is not appropriate in this case.
If I selected English as the language, and then I selected the Issue to be "Hello" (not in Sample List A, but for example). Consider "Hello" as Equal to "A"
C3 and C4 are dependent upon the input of C2 and will automatically provide Sublist A and SubsubA without any additional input on my part. This is critical and a part of the reason why I am not using dependent drop downs.
Suppose tomorrow, I open the file, and a spanish person will be viewing it. I don't want to go back to the issue and re-input for that individual. I simply want to select the language and have it automatically adjust. Thus, when I select "Other" language, the Issue in C2 automatically changes to "Hola" (also not in Sample List B, but would be in the corresponding row as "Hello") Consider "Hola" as equal to "1".
If this changes, C3 and C4 will automatically adjust accordingly.

Language = English
C2 = A
C3 = a
C4 = A.a

Language change = Other
C2 = 1
C3 = 1.1
C4 = 1.1.1

Eventually, I will have hundreds and potentially thousands of issues entered into the log. If I had to change the issues every time I toggled between languages, it would be impractical.

[DaBrot791]

Still working on it, please note the other forums where this problem has been posted.
http://www.mrexcel.com/forum/excel-q...een-lists.html <-posted first but no responses.
http://www.excelguru.ca/forums/showt...ed=1#post26248
http://www.ozgrid.com/forum/showthre...416#post774416

[fshbet]

Hi DaBrot791,

Please find the attached for solution.

[DaBrot791]

Just a note, but the solution I was looking for is here in case someone else is interested in the future.

http://www.excelforum.com/excel-prog...ace-value.html

Thank you for all of your help in setting me in the right direction.