Dependent drop down list and hide used items

Yweiss0

New Member
Joined
Aug 3, 2013
Messages
46
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,843
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Yweiss0

New Member
Joined
Aug 3, 2013
Messages
46
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,843
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

New Member
Joined
Aug 3, 2013
Messages
46
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

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
1630582492970.png


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

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:
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,061
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,036
Messages
5,767,766
Members
425,431
Latest member
Sayson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top