I have a large (for me) excel table of about 16,000 rows and 10 columns. The autofilter on the columns function works fine for my needs of seeing specific information, but I need to extract these variously filtered tables to their own sheet, and have those sub-sheets update themselves as the master sheet changes. The problem is, most of the columns have an enormous number of options, so when I think about using the advanced filter option, it's daunting, and there has to be a better way.

For instance, my column B filter would have 20 items selected, and then with that result my column C filter would have consist of 50 items I select (I end up selecting all for that column c, then de-selecting the few I don't need.... Way easier). There are never any "or" scenarios I need, so that makes it easier somewhat. I only need "and" (these items in this column, and from that result, only these items in this other column, etc).

So how do I go about doing this? Is there a way to advance filter and for the criteria, somehow designate "all items EXCEPT..."?

The way I have it now is not dynamic. My master table has everything and, and I've manually extracted the 11 sub-tables/sheets I need. But if I change an items value on the master sheet for any column, I have to manually go change it on every sub-sheet... It's a nightmare.

Help! I'm all googled out on this.

I should add that all of the filter options for each column are text, not numbers, except for the last column. So nothing as easy as "0"....more like the equivalent of" bacon, bread, butter, ham, [insert 30 more items here], but just not cheese and milk"