Dependent drop down list and hide used items

[Yweiss0]

Hi,
I want to make a dependent drop down list but I want to add to it also the ability to hide the used items.

I will explain it a little bit:
I have a liat of workers and some workers can work only with some workers.
For example
DAVID FRED TINA SAM
Jhon Toni Jhon Tina
Sam Tina Anna David


So I want that if I will choose David in the cell under it I will see in the drop down only the bames that related to him (jhon and sam)
But I want that if I used David I then will not see his name, I dont want to see his name even if I will choose someone that can work with him (like sam for example) but since I used david his name should be removed from the drop down list.

I saw how can I make the hide used items and how to make dependent list but I cant find a way to join them together

Thanks for the help

 

[Eric W]

You won't see David in the dependent drop down, since he's not in the "David" named range. Only Jhon and Sam are. The named ranges start at row 2. It all depends on how you set up the ranges.

 

[Yweiss0]

You won't see David in the dependent drop down, since he's not in the "David" named range. Only Jhon and Sam are. The named ranges start at row 2. It all depends on how you set up the ranges.

But if will choose in cell A2 Tina then in cell B2 I will see David in the dependent drop down because he is writte in the Tina name range even though I've already used his name in cell A2.
and I have about 7 pairs of cells to fill. Because of this I want that if I used David no matter in which cell I will not see his name and I will not use his name again

 

[Eric W]

Ah! I see, you have more than 2 drop-downs. You have 7 pairs of drop-downs? This is still possible without VBA, but it gets more complicated. You'd need 14 columns (7*2), plus the existing list of people who can work together. It would also be very possible that some drop-downs would have an empty list since the available people have already been selected. Given the number of people, and the number of lists you have, this might slow down your sheet, although 40 names shouldn't be too bad. A VBA solution might be better. See if jorismoerings can help you port his macro to your sheet. Let me know if you want me to build a formulaic version.

 

[Yweiss0]

Ah! I see, you have more than 2 drop-downs. You have 7 pairs of drop-downs? This is still possible without VBA, but it gets more complicated. You'd need 14 columns (7*2), plus the existing list of people who can work together. It would also be very possible that some drop-downs would have an empty list since the available people have already been selected. Given the number of people, and the number of lists you have, this might slow down llyour sheet, although 40 names shouldn't be too bad. A VBA solution might be better. See if jorismoerings can help you port his macro to your sheet. Let me know if you want me to build a formulaic version.

Actually I have 7 pairs and another more 4 separate cells but they also need to be checked if one of the names is there.
I'm still want also a formula version please, even if eventually it will be only for learning and getting to know how to make it possivle. ?

 

[jorismoerings]

The solution is working like a charm!
But I dont know how to use it in my project.. how can I use the code on another workbook , its quite difficult for me to understand how to take the code and make the right changes to use it in another workbook

There's no need to change the code because the code will automatically create all the named lists you'll need and delete the one you don't need.

The only thing you need to do is:

1. Insert the worksheets named "lists"and "Table" into your other workbook
2. insert the code into your other workbook.
3. the code will provide 2 lists which can be used in data validation (ic "Data_val_List_1" and "Data_val_List_2")
4. the lists sheets will contain formula's to make sure all the necessary lists are available AND the structure to hide a name if it's already being picked from either list or if it's not valid based upon the structure of your table.
5. the VBA part does only create all the lists needed for the structure because you've told it would be a substantial amount and not just 4 as in your example.
6. Data_val_list_1 will contain all names you've mentioned on the first row of your table. See the yellow part
7. Data_val_list_2 will contain all names you've mentioned below the first row.

So if you've done the above, just apply the correct list to the data validation into the sheet you need it, like this:

or like this

depending on which list you'll need to choose from and it should work.

So for learning experiences, just look at the formula structure on the Lists sheet after you've run the code.

Hope this helps:

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dependent drop down list with hide used items - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

Also posted here Help with dependent drop down list with hiding already used items