View Full Version : Selective Filtering

02-22-2006, 11:37 AM
At present i have created 4 listboxes, using data validation and 'indirect', which based on list1 provides list2, which provides list3, and then list4. using advanced filter, i finally get 12 months of data. this part works well.
however, i have also added "all" to lists 2,3 and 4.
how can i, if "all" is chosen, lets say in list2, that the filter is unique based on list in list2, and not a combination of list1,list2, list3, and list4. if i select "all" in list 2, and there are 3 unique items in list, but in combination with list3 and list4, it comes back 12 times. i would want it to come back with only 3 items in list 2, and as a result, only 3 items in list 3 and 4, with the appropriate data rolled up
same logic should apply if "all" is chosen in list3 or list4.
basically, the column that has "all" should return a unique list, and subsequent lists, should be filtered based on the unique list proveded. i hope this makes sense.

if "all" is not chosen, in any of the listboxes, let the normal advanced filter process prevail, (which works for me).

can this be done? i appreciate any help you can provide.

02-22-2006, 04:49 PM
Not exactly sure I follow you, but if you are using Indirect to identify a list, make sure something like a named range (if not for each of them) references "All" so that when that is selected in any box, it returns all records for that one list...

02-23-2006, 06:45 AM
i have done that. but when i select "all", there could be, for arguments sake, 6 unique entries in the list. however, in running the advanced filter, and selecting "unique", that list, in coordination with 3 other lists, could return 20 instances of the 6 unique items. what i would like, is if, "all" is selected, return the unique for that list. sort of a complicated if statement. thanks.

02-26-2006, 03:49 PM
just a thought. could someone possibly help me with a macro, based on an "if" statement.
if cell contains the word "all", do advanced filter based on one criteriea, the cell with "all". if word does not appear, do advanced filter, with four criteria, which i have working at present. again, i would appreciate any help provided.

02-26-2006, 04:02 PM
Can you attach a sanitized version of your workbook? It may be easier to set up a named range solution than you think...if you would prefer not to rely on code...but either way, sample data to work with is helpful..

You can attach the file by going advanced>manage attachments.