Cancel filter function if cell blank

[Ngreen2571]

Afternoon hive mind.

I current have a simple filter function up and running for filtering out the categories of crime. I'm using an active X combo box for my list to filter. What I'm wondering if, is there any way for it to return all results when my combo box is blank? Reason I ask if I then have a countif dashboard running from these results and returning all results would give everyone the high level figures.

Thank you in advance.

 

[RoryA]

Welcome to the forum.

Yes there is, but you haven't given us any idea of your code/formulas, so it's hard to be specific.

 

[Ngreen2571]

Welcome to the forum.

Yes there is, but you haven't given us any idea of your code/formulas, so it's hard to be specific.

Morning.

The formula I have up and running is as follows
=filter('search data table'!A1:O5612,'search data table'!M1:M5612=B2,"No Results")


B2 being the cell my combo box is linked to as a search field. Hope this helps? Thank you in advance! Let me know of anything else is needed.

 

[RoryA]

You should be able to use something like:

=IF(B2="",'search data table'!A1:O5612,filter('search data table'!A1:O5612,'search data table'!M1:M5612=B2,"No Results"))

 

[Ngreen2571]

I knew it needed nesting in an IF function I just couldn't figure it out! Thank you it works!

 

[RoryA]

Glad we could help.

Just for future reference, in case you ever have a similar issue with multiple criteria, you could also do something like this:

=filter('search data table'!A1:O5612,('search data table'!M1:M5612=B2)+(B2=""),"No Results")

but for just one criterion it makes more sense not to bother filtering at all if the criterion is blank.