Populate Dropdown selection options based on specific table value

[Ash4741]

Hi I am new to excel formulas and am trying to figure out how to make it so that the data from one table will show in a dropdown selection menu but only the data that is filtered based on predetermined condition. For example: I have an employees table that lists name and department. I want the dropdown to only show the employee names from a specific department that is already predetermined and does not need to be typed in. This dropdown menu populates with names of all employees in the fabrication department for example.

Thank you everyone!

[RChad]

Hello,

Without having your data I mocked up a worksheet. This was a 2 stage process. The first process was to filter the available names from the department selected using this formula:

=IFERROR(INDEX(Table1,SMALL(IF(Table1[Department]=$D$2,ROW(Table1[Department])),ROW(1:1))-2,2),"")

This is an array so press CTRL+SHIFT+ENTER to get the curly brackets and drag down your range.

Then I created a named range called 'Names' and have this formula:

=OFFSET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$E:$E),1)

In a cell for your data validation list enter this:

=Names

I hope this helps

[Ash4741]

Thank you so much! This is perfect!!

[RChad]

You're welcome!

Please marked as solved.

[Ash4741]

Actually one more question. I was wondering if there is a way to remove the blank entries from the drop down?

[RChad]

Hi,

Sorry about all of those blanks. Try this formula instead for the named range:

=OFFSET(Sheet1!$E$2,0,0,COUNTIF(Sheet1!$E:$E,">*"),1)

This should be better but I can't figure out how to remove all blanks from the drop down. This will still have one blank at the bottom which is an improvement.

Can you please mark this thread as solved? It is under the 'Thread Tools' option. It would be nice if you can add to my reputation too. I would appreciate it.

Have a great day ahead!

[Ash4741]

Thank you! This is good