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