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.