MS Excel - Add only selected values in drop down list

Question

My first question about Excel as I was assigned to create a report. After watching videos from I was able to create a drop down list. However I am not getting how to add only selected values in my drop down.

Here is how my data looks like:

Emp Name        Title
-------         ------
Name 1          HR
Name 2          HR
Name 3          Manager
Name 4          HR
Name 5          Manager

I want to create a drop down list where I want to keep the HR names

I am sorry, I don't know how to add code or give more information here about what I have done so far. Thanks in advance for your help

Answer 1

You can't do it directly. You have use a helper column. In below screenshot I have put following formula to F2 cell to filter names of HR department.

=FILTER(A2:A6,B2:B6="HR")

Then use =F2# to data validation rule as list.