Solved: Creating Drop Down Lists that Filter

[J Srdarov]

Hello All,

First post on here.

I have spent the afternoon perusing your board and can't find what I am looking for, so have decided to post on the issue. I have been going crazy looking through vba books etc to find a similar example that I can adapt to my situation but havent been successful.

I am attempting to create a simple spreadsheet report that contains 3 seperate filters (in the form of a drop down list) at the top of the page for the following variables:
- Year
- Region
- Age Group

I have attached a small sample to this post of what I will be working with.

Since the columns that contain the data that will be filtered on will be hidden (columns E - G) I cannot use the autofilter or advanced filter options and therefore need some type of macro. The macro must be accurate in the sense that it will enable a person to make say a selection in any of the drop down filters and then the rows of data that don't correspond to the selections are then hidden. The user will then be able to make numerous changes to the filters as per their desire when analysing the data. Each filter will ideally also contain the value All which will enable them to look at say all age groups within the data range. I would also really like some sort of drop down arrow displayed to the right of each drop down list filter.

I will be working with Excel 2003.

Is anyone able to :help

Thanks
Jamey

[unmarkedhelicopter]

You have a couple of options (3) here :-
1. you can have the filters selected in order and have a hidden field which says if a record coplies to the filters selected and apply a filter in place to view those records. You'd need a clear button to reset filters.
2. you have a second sheet for the filtered answers, again built incrementally again with a reset button.
3. you can have exactly what you've asked for (with filter applied to a second sheet (again)) but dynamic selection (and order) of filters including ALL, but this would be constructed on a form which would be fired up from a button

do you want a complete solution or are you willing to do some of the work ? (if so what is your programming ability ?) Oh ... and how big is the list likely to be ? If it's gonna be big (ever bigger than (say) 30,000 records then I'd recommend putting the list into say an access table and get to it from excel (we could write an edit/update utility sheet aswell) (I know excel2007 can have upto 4,000,000 rows but can you imagine the processing required to handle all that data ???))

Your "ALL REGIONS" bit 'may' be a bit of a problem as when searching for 'NORTH METRO' this would not be returned so this 'may' be best to have a separate record for each region (else we'd have to do some hard coding which is always a maintainance issue (if you get a new region ? or drop an old one ?)). And what would you do if you had "ALL regions but NORTH" ??? you'd have to enter for EAST, WEST, SOUTH and CENTRAL anyway ...

[Charlize]

Something to play with.

[J Srdarov]

Thank-you for responding unmarkedheli and Charlize,

The list isnt going to be big - shouldnt be more than 500 so it can stay in excel.

My programming experience with VBA is minimal, but I was hoping that someone has done a similar thing and posts their code they used and then I would try to figure it out from there and apply it to my data set, right now I dont know where to start at all.

Charlize I cannot view the attachment because it says my post count is 0 momentarily?

Thanks Again,
JS

[J Srdarov]

Now I can view it Charlize, that looks fantastic, I will play around and learn something, cheers for that!

[tccmdr]

Don't forget to markthe thread as "Solved":thumb

[J Srdarov]

Hi Mike,

Will do once I have successfully used it in my document.

Thanks
JS