Forum Discussion
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. If I type a zero, it will display "No records Found!!!" which is fine. I would like to have the output from search box blank when it's empty?
Here's the formula I'm using:
=FILTER('2022 Submissions'!A3:V799,LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1,"No records Found!!!")
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:
5 Replies
Try
=FILTER('2022 Submissions'!A3:V799,(G1<>"")*(LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1),"No records Found!!!")
or
=FILTER('2022 Submissions'!A3:V799,IF(G1<>"",LEFT('2022 Submissions'!C3:C799,LEN(G1))=G1),"No records Found!!!")
- 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!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:
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
