I have just started using Excel at the office so this is fairly new stuff for me.
My aim is the following - hopefully you can advise me on a good way to approach this.

We have an online system that generates xls dump files. These essentially contain a table with lots of information about every test case we perform. From this table I am interested in three fields: TestID, Location and TestCaseStatus. With these fields I need to generate :
  1. A drop down menu to filter by location
  2. A table containing the count of each test case status for the relevant location. Every Row represents a TestID and the columns a status.

I was hoping you would be able to give me an idea of a good way to tackle this. My initial thought is that SQL maybe useful to import the desired table columns and then further filtering would have to be done through nested for loops. I'm sure you can suggest a better way though.

Also, if possible, I would prefer the raw data not to be displayed in my analysing spreadsheet - only the summary should be shown.

Any ideas?