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

 

[jorismoerings]

Hi,

I have a few questions which are necesarry for answering your question:

1. Is your list just an example or is this the complete list?
2. Are you open for a combined solution using VBA?
3. What is the Office/Excel version you're using?

 

[Yweiss0]

1. This is just an example the real list contains more names (about 40 names)
2. Yes I'm open for combined solution with vba
3. My excel version is 2019

Thank you

 

[Fluff]

My excel version is 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jorismoerings]

Hi,

1. This is just an example the real list contains more names (about 40 names)
2. Yes I'm open for combined solution with vba
3. My excel version is 2019

Thank you

1. I already assumed this was going to be your answer hence the VBA question
2. ok
3. Follow @Fluff advise and update your account details with the office version.

The approach:

1. I created a workbook with 3 worksheets:
   1. data entry is a worksheet with the datavalidation in column A & B, the data validation part relies on sheet 2
   2. Lists is a sheet which is created by the VBA part which does nothing more than 'take the initial list with names and transform them to usefull range which subsequently can be used in data validation. Do not add or modify this sheet because the VBA will rebuild this sheet.
   3. Table is a worksheet which containes the initial table. Do not insert header or change it otherwise because it will mess up the VBA.
2. Way of working:
   1. Copy the list with people to the table sheet. Line 1 of the sheet is the worker name, the cells underneath containing the names he/she can work with.
   2. Click the "Create Lists" button which will run the VBA.

I will not copy the full VBA code and all the range names but will attach my test version in stead. This is the link

Try this and see if this helps you.

 

[Yweiss0]

Thanks

 

[jorismoerings]

No problem, glad i could help.

Just remember our suggestion to update your account details

 

[Yweiss0]

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

 

[Eric W]

You can do this fairly easily without VBA. Consider:

Book4 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	Toni	Fred		David	Fred	Tina	Sam	Jhon	Toni	Anna
2				Jhon	Toni	Jhon	Tina	David	Fred	Tina
3				Sam	Tina	Anna	David	Tina
4
Sheet7

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=AllNames
B1	List	=INDIRECT(A1)

Create a list of names (D1:J1), and underneath, the list of people they can work with. Select D1:J1 and create a named range out of it called AllNames. Then you can select D1:J3, and click "Create from Selection" on the Formulas tab. Check the "top row" box only. Then you can use the very basic Data Validation formulas in A1 and B1. If you have names with more than one word (like "Mary Ann"), we need to tweak it a bit. The B1 formula would be

=INDIRECT(SUBSTITUTE(A1," ","_"))

 

[Yweiss0]

You can do this fairly easily without VBA. Consider:

Book4 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	Toni	Fred		David	Fred	Tina	Sam	Jhon	Toni	Anna
2				Jhon	Toni	Jhon	Tina	David	Fred	Tina
3				Sam	Tina	Anna	David	Tina
4
Sheet7

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=AllNames
B1	List	=INDIRECT(A1)

Create a list of names (D1:J1), and underneath, the list of people they can work with. Select D1:J1 and create a named range out of it called AllNames. Then you can select D1:J3, and click "Create from Selection" on the Formulas tab. Check the "top row" box only. Then you can use the very basic Data Validation formulas in A1 and B1. If you have names with more than one word (like "Mary Ann"), we need to tweak it a bit. The B1 formula would be

=INDIRECT(SUBSTITUTE(A1," ","_"))

This solution will make the dependent drop down list but it will not hide the already used items..
I want that if i already used David in one of the cells I will not see David in other cells.
If you have a solution how to hide used items I would very happy to see and use it