Forum Discussion

Copper Contributor

Sep 03, 2022

Solved

Activex Search box

Hello everyone, I created a search box using activex box with a filter function to search for various text in a table. The issue I have is, when the search box is empty it displays the entire table...

excel

Formulas and Functions

HansVogelaar
Sep 04, 2022
LouP629 One option is to use

=IF(FILTER('2022 Submissions'!A3:V799,(G1<>"")*(LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1),"No records Found!!!")="","",FILTER('2022 Submissions'!A3:V799,(G1<>"")*(LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1),"No records Found!!!"))

Another is not to show zero values, in File > Options > Advanced > Display options for this worksheet:

HansVogelaar

MVP

Sep 03, 2022

LouP629

Try

=FILTER('2022 Submissions'!A3:V799,(G1<>"")*(LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1),"No records Found!!!")

=FILTER('2022 Submissions'!A3:V799,IF(G1<>"",LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1),"No records Found!!!")

LouP629
Copper Contributor
Sep 04, 2022
Question, Is there a way to leave search results blank instead of populating with zeros?
I have blank fields and they get populated with zeros, not a big deal but would be cleaner!
- HansVogelaar
  MVP
  Sep 04, 2022
  LouP629 One option is to use
  
  =IF(FILTER('2022 Submissions'!A3:V799,(G1<>"")*(LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1),"No records Found!!!")="","",FILTER('2022 Submissions'!A3:V799,(G1<>"")*(LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1),"No records Found!!!"))
  
  Another is not to show zero values, in File > Options > Advanced > Display options for this worksheet:
  - LouP629
    Copper Contributor
    Sep 04, 2022
    works great, again thank you
LouP629
Copper Contributor
Sep 03, 2022
HansVogelaar
Hi Hans,
Thank you for your quick response. The first answer works perfect and what I was looking for.
The second answer gives an error like and is a bit ambiguous.
Thank you