I had a real face palm moment the other day which, having no shame, I thought I’d share.
I imagine we all know that when using an advanced filter you can use standard criteria with a criteria range (headers match the column names and you enter values as the criteria for each column) and you can also use formulas as criteria when things are more complicated (in this case the headers should NOT match a column header. I usually just leave them blank).
Remarkably, it had never occurred to me that you could combine the two at once. Yes, I know – I’m an idiot. But there you go. You can. It’s actually really useful – especially if you want to have multiple options on one or more fields while using several criteria fields, where with regular criteria you would have to have a row for every combination of options you wanted. This is particularly useful if you are using code to filter data and want to allow users to select any combination of options.
Raw data file:
Simple advanced filter:
Note that Item1 filter only applies to Jim
Corrected the criteria so Item1 applies to both:
Adding more options and you can see how the criteria rows start to multiply:
So let’s use a formula as criteria (note row 9 is the first row of data, NOT the header row):
And finally, let’s combine the two methods:
I leave it to you to work out what the criteria range would look like for this permutation if you didn’t use formulas… 🙂