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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,877
Office Version
  1. 365
Platform
  1. Windows
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

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

ADVERTISEMENT

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.
 
Solution

jorismoerings

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

ADVERTISEMENT

No problem, glad i could help.

Just remember our suggestion to update your account details
 

Yweiss0

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,678
You can do this fairly easily without VBA. Consider:

Book4 (version 1).xlsb
ABCDEFGHIJ
1ToniFredDavidFredTinaSamJhonToniAnna
2JhonToniJhonTinaDavidFredTina
3SamTinaAnnaDavidTina
4
Sheet7
Cells with Data Validation
CellAllowCriteria
A1List=AllNames
B1List=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

New Member
Joined
Aug 3, 2013
Messages
46
You can do this fairly easily without VBA. Consider:

Book4 (version 1).xlsb
ABCDEFGHIJ
1ToniFredDavidFredTinaSamJhonToniAnna
2JhonToniJhonTinaDavidFredTina
3SamTinaAnnaDavidTina
4
Sheet7
Cells with Data Validation
CellAllowCriteria
A1List=AllNames
B1List=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
 

Forum statistics

Threads
1,148,011
Messages
5,744,334
Members
423,863
Latest member
teehexcel

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