Advanced Filter--Blanks

kevinonexcel

New Member
Joined
Jul 15, 2010
Messages
13
Hello,

I am using an advanced filter for a large database. The filter criteria are dependent upon another worksheet. In some cases the criteria in a column is blank. This is done by using an IF function and "". When ever a column is blank, I don't want their to be any criteria is that column. However, whenever I try filtering a case with a blank no solutions appear even though their are valid options. Do I need to add something between my quotations to filter a blank?

Thanks for any help
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,871
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
"="

P.S.: "<>" for Non-blanks
 

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
Use a computed criteria -- if the column of data which you're checking for is A, then have a formula like this
=LEN(A2)=0
in H2, for example, and use H1:H2 as the criteria range (where H1 is blank)

Bob Umlas
Excel MVP
 

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
using just = as the criteria will NOT find cells which evaluate to this blank:
=IF(A33=5,4,"")
 

kevinonexcel

New Member
Joined
Jul 15, 2010
Messages
13

ADVERTISEMENT

Thank you for the response,

I tried it but it didn't work

The database has several columns with x's when the row is valid for the column's criteria. Here is an example of the formula in one cell of the criteria.

When sheet1E5 does not have a value of "Products", "=" appears in the cell but the filter returns no matches even though their are matches

HTML:
=IF(Sheet1!$E5="Products","x","=")
 

kevinonexcel

New Member
Joined
Jul 15, 2010
Messages
13

ADVERTISEMENT

Thank you for the responses-- still no luck tho.

I tried using the LEN function and that returns True or False. If it could return an X then it might be usefull to me.

I also tried using "=""" and it just returned =".

Any more ideas would be appreciated
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,113
Messages
5,857,454
Members
431,880
Latest member
kar2rost

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top