Create (or filter) table containing subset of data from master table depending on user dropdown selections

[rasbiofish]

I have a need to automatically create (or filter) a table in a new worksheet that contains a subset of data from a master table, dependent on choices made in a number of drop down list boxes. I have already constructed a workbook that at present has two worksheets.

1.The Data worksheet contains my master table which lists engineers, their locations, and then skill level in each technology. The master table is not yet fully populated, nor does it have the full set of columns or rows it will eventually have, but should have sufficient information to develop and test the functionality I am looking for.


2.The Form worksheet contains three drop down lists (created using Data Validation) and a source table for the list choices. The worksheet also contains some VBA code which clears downstream choices if an upstream choice is changed.


What I am trying to do is get Excel to create a table (or filter a copy of the master table) in a new worksheet that lists only those engineers who have a skills in a particular technology. The user would open the worksheet, choose a Field, then Specialization, then Technology and then click a button to cause Excel to output the results in the new table. I need to strip out any engineers that do not have skills in the selected technology (i.e. their cell contains None or is blank).
I have defined names and tables within Excel. I have created a name (col_Tech) which contains the contents of the correct column of the master table (tbl_Data) that matches the selected technology, but do not know how to get Excel to output this information along with the engineer's names and locations in a new table.


I have tried to use Advanced Filter but I can't get it to output what I want. I think this may because I want to display a particular column with only rows that do not contain blanks or None. Most of the examples for Advanced Filter do not relate to what I am trying to do.


Any and all help is greatly appreciated.

 

[ghrain22]

My first reaction was to do an advanced filter. I'm sorry that didn't work. Maybe try looking into a pivot table with slicers or just the default filter drop down. The only alternative I can think of along the lines you're looking for would involve a macro.