How can I create and save multiple auto-filters for Sheet1!A:A (contains a LOT of non-unique text values) using data stored in rows on Sheet2 as criteria ranges (a separate filter using each row, that is)?
I want to create a drop-down list populated with values of Sheet2!A:A. When a specific value is selected, values in the corresponding row should be used as a criteria-range for filtering. And if at all possible, this shouldn't mess with the Undo stack.
Nope, that's not it. If filtered by "Central America", results should read: "Mexico | Item 1", not "Country", "Guatemala", and "Mexico" and there shouldn't be any residual junk like those 0's. Besides, rows on Sheet2 are VERY different in length, and I wouldn't want to slow filtering to a crawl by including thousands of empty cells in the criteria range.
Yes, it is it. I've filtered by Central America .... here are the results. The dynamically defined name excludes the zeros, so go back, and read what I said ... properly. Besides, you have no idea how fast Advanced Filter is ... even if the zeroes were included, you would not be able to tell the different.
GlennUK, Fluff, thank you guys. Your solutions work but they are way too clunky. I need to do this kind of thing A LOT, and there's just too many steps before you get the results and there's a helper column in a worksheet that's already huge. I'm looking for a one- or two-click solutions and zero additional clutter. Even more importantly, I need to be able to filter the results again using auto-filters on other columns and with these advanced filter routines I don't see how I can. Anyway, thanks again for your time.