• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Double Selection drop down

P

Pofski

Member
Hello everybody,

I am trying to achieve the following.
I have in column A a list of names. In column B, C and D i have a list of languages these ppl speak.
Some speak 3 languages (or more), some only 2.

Now on a separate page i have a list of source languages (A), and a list of target next to it (B).
I would like to have a drop down list in column C with only the ppl who are eligible for the translation.

Also, if it would be a language that nobody in the list speaks, it would give a warning message in the cell.

Sincerely
 
Hi Pofski ,

Now that this forum has the facility of uploading files , please make use of it ; your explanation is very clear and understandable ; however , anyone wishing to answer will probably have to create a workbook with some data ; if you can upload a sample file with a reasonable set of data , it will help.

Narayan
 
Hi Pofski ,

Is this acceptable ?

I just went through your initial post , and saw that you want a drop-down in the Translator column ; if this is to be provided for each combination of languages e.g. O6 and P6 , then O7 and P7 , it means that the number of language combinations needs to be fixed i.e. will your language combinations be only in the range O4:p10 ?

Narayan
 

Attachments

  • Translations.xlsx
    20.7 KB · Views: 9
Hi Narayan,

thank you already for your example.
The idea is that eventually all the information (tables and languages are on a different tab, and that the O3 till O10 would be part of a growing list.
Every time that there would be a new project, we would add to the list, and translators could come and go.
If we have translators who move on, we would need their names to remain in the cells of the projects that they did, and if we add new names, depending on their skills, they should also be eligeble for projects.

Again, thank you for the help already, and sorry for the late reply. (weekend with the in laws ;) )
 
How about a helper column added to Narayank's table?

Code: 
=IFERROR((MATCH(Source,$B2:$G2,)>0)*(MATCH(Target,$B2:$G2,)>0),"")
And copy down for every translator.

Now you have a 1 if a translator knows both languages selected.
 
Hi Pofski ,

1. Since your data is in the form of a table , the formulae used will not need changing as and when you add new languages / translators , provided they are modified slightly from their present form , to make the number of columns dynamic. At present , I have used the construct {1;1;1;1;1;1} since there are 6 language columns in your input table.

2. Since you say that for each combination of languages in the output table , the translator cell should have a drop-down , this will have to be dynamically derived ; this can be done using VBA. Is this acceptable ?

3. The whole task is an interesting one , but will take at least a couple of days , if not more , to complete ; is this OK with you ?

Narayan
 
Hi Narayan,

The use of VBA will be the only solution in the end i think. The other request i have made on the same day is also to be integreted with this document, so i guess VBA will be unavoidable.

If it takes a couple of days, it won't be a problem.

Thank you for your effort

Pofski
 
Hi Narayan,

I was just wondering if you might have made some progress with this issue.

Thank you in advance
 
Hi, Pofski!
Give a look at the uploaded file. I converted all ranges to tables, defined a few dynamic named ranges for using in data validations and searches, and not much more. Give it a try and just advise if any issue.
Regards!
 

Attachments

  • Double Selection drop down - Translations (for Pofski at chandoo.org).xlsm
    35.4 KB · Views: 4
You must log in or register to reply here.
Back
Top