Project - Populating from other sheets via drop-downs, etc

Hey everyone.

I am by no means a programmer, and have only done slight formulas in Excel for previous documents. But I have a form I need to create and I was hoping someone can help me in the right direction on the best way to accomplish this.


Currently I have an excel document with 100+ names on it. Each row has their name in a column, phone number in another, city in another, email, etc... Also is a column is their job qualification and their ranking within their job.

So for example here is how it is laid out:

John Doe | xxx-xxx-xxxx | Anytown, USA | xxx@xxx.com | Engineer | 2
John Doe | xxx-xxx-xxxx | Anytown, USA | xxx@xxx.com | Integration | 4
John Doe | xxx-xxx-xxxx | Anytown, USA | xxx@xxx.com | Systems | 1
Bob Dole | xxx-xxx-xxxx | Notown, USA | xxx2@xxx.com | Engineer | 4
Bob Dole | xxx-xxx-xxxx | Notown, USA | xxx2@xxx.com | Integration | 1
Jane Dow | xxx-xxx-xxxx | Somewher, USA | xxx3@xxx.com | Systems | 1
Jane Dow | xxx-xxx-xxxx | Somewher, USA | xxx3@xxx.com | Engineer | 3

Etc..

So some names are duplicated based on if they have multiple qualifications. Their ranking (a number from 1 - 4) is a reference to us on how strong that person is in that specific field.


What I am looking to do is this. I want a main sheet in excel to have basically 3 pull down menus. One for Qualification (about 8 different categories total), one for Ranking (so if someone puts they want a ranking 3, it will show all rankings 1 through 3 (1 being highest) and not show any 4's). The final pull down would be location. Once the user selects those three options, it populates a list on the main sheet with those attributes you specified in the drop-downs.

I know to do this, I need to have a sheet populated with all that info, which I sort of already have. But should I reorganize it so there is a column for each qualification? If I do that, how is the ranking calculated?

If you have any suggestions as how to make this work, please let me know.


I appreciate your time,

Marc

The final list is a snapshot of people that fall under the category and rank the user is looking for. For example, if we need a rank 3 or higher Engineer in Anytown, USA, the user would just select from the 3 drop downs:

Engineer | Rank 3 | Anytown, USA

And the list would populate all engineers rank 3 or higher located in Anytown, USA.


Yes, filters is how the document is used now. I am looking for a cleaner way of presenting the information without the user seeing or having access to the "master list" of names. Just a list populated by the users requests.

-Marc