How to stop Excel Advanced Filter from recognising 0 as a criteria

[shirleyj]

Hi Mr Excel,

My criteria table is set up to work with some basic formulas. If the result of the formula is a blank or 0, Excel still treats this as a criteria. Unfortunately this means the advanced filter does not work as it should ... ie it returns 0 filtered rows. I am guessing that treating 0/blank as part of the criteria is a limitation of Excel's advanced filter? Just wondering if anyone knows any simple workarounds to this?

Many thanks,

Shirley

 

[mikerickson]

A formula that returns "" in the criteria range is treated like an empty cell (i.e. all rows meet that condition)

 

[shirleyj]

Hi mikerickson,

I tried your suggested approach, but it is producing some unexpected results.

In my criteria table, I have two selection criteria – type and category. The idea is to have the criteria table used by the advanced filter on a sheet hidden from the user. When the selections are inputted on the selection sheet, I used the below formula to copy these to the criteria table on the hidden sheet:

=IF(type=””,”” ,type)

This formula works - if the user selects a type, it is populated on the criteria table and they don’t, it returns a blank.

The problem comes when I select a category, but try to leave the type blank. The result of the advanced filter is that it only returns the category rows that have something selected in the type – and misses out the category rows that have nothing selected in type (for example, it returns two rows, when I am expecting five).

Unless I am missing something, I think this is a shortcoming of Excel’s Advanced Filter … it only works well with databases that are fully populated, but not so great when there are some blank fields. Does anyone know if I am doing something wrong? I know the problem sounds very complicated, but I hope the solution is easy.

Best regards,

Shirley

 

[mikerickson]

Another way to indicate "everything" is '=*

 

[shirleyj]

That does not work either. The only simple solution I could think of is to always ensure that the 'type' column is populated. Weirdly, if the 'type' column is populated and the 'category' is not, it works great, but if the category is populated and the type is not, it doesn't. I really believe this is just a shortcoming of the advanced filter. It could probably be worked around with some VBA code, but that's way too complicated for what I am trying to do.

Thanks for your responses ... much appreciated.

 

[mikerickson]

How are these cells populated, VBA or formula?
If formulas what are they? Might there be a space instead of a zero-length string?