Please help... Trying to make auto filter with check boxes

[ElkySS]

I figure this should be pretty easy but I just can not figure it out. And Google is being no help to me today.

I am trying to make a dropdown that works like autofilter does except allows me to pick more than one thing from the list. I am picturing something like a drop down that has check boxes in it so I can pick several dates from the list and see data for only those dates.

Data gets added to this sheet every day so it would need to be added to the drop down as well. I am not sure if all of this will need a macro or if there is some easy check box somewhere in Excel to enable it. If somebody needs me to I can send you a copy of the sheet I am working on with some dummy data in it.

Thank you all for taking a look and any help would be grand.

 

[crimson_b1ade]

doesn't autofilter do that?

 

[ElkySS]

All I can make it do is a list of anything that is under it. From that list I can only select 1 date. I was wanting it to be done in a way that will allow me to pick several dates at the same time. so I could see data from 3/12/2009, 3/13/2009, and 3/14/2009 rather than having to look at them one at a time.

The reason I would like to be able to do this is averaging formulas that I have. Right now I am using
<CODE>
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L6:L19979,ROW(L6:L19979)-MIN(ROW(L6:L19979)),,1))*(L6:L19979="Yes"))
</CODE>
to look at any data that is being displayed from the auto filter and giving me the average. There is another field looking for No and a total of those. I use those numbers to come up with a % and % to goal. With the standard auto filter I can only see the average for the entire month or for the one day that is picked. Would be better if I was able to pick the days of the pay period and average it that way.

 

[Weaver]

How about using a new column with a formula that flags the dates you're interested in then filter on that column?

 

[ElkySS]

This sheet is used by about 50 people and 49 of them will have to learn how to use a check box... lol Not quite that bad but they are not very computer savvy.

I know that I have seen check boxes in a drop down but I don't know how.

 

[Tom Urtis]

If you want a "drop down" by which you can select more than one item, then instead of data validation or a combobox (which I assume you have tried), use a listbox from the control toolbox.

From the control toolbox toolbar, click onto the listbox icon, then draw a listbox onto your sheet where you want it to be. Right click in the listbox you just created and left click on Properties.

In the Properties window, fill in the ListFillRange field for where you keep your list of criteria data, example, maybe you keep it in X1:X10, so type in
X1:X10
for the ListFillRange property.

In that same Properties window, for the MultiSelect property, select 1-fmMultiSelecMulti.

In that same Properties window, for the ListStyle property, select 1-fmListStyleOption.

Close the Properties window and exit Design Mode (there is an icon for that on the control toolbox).

Now, you are ready for a macro that would loop through the selected items and filter...

either

one at a time

or

up to 2 at the same time as Or

or

up to 2 at the same time as And

or

use AdvancedFilter to filter for all at the same time for however many criteria items were multi-selected in the listbox.