Dynamic Search Box in Excel

[bhenlee]

Hi Experts,

How can i create a dynamic search box which will display all the rows that have the data that is being search (it can be dynamic as i type or after pressing the search button).

Say I put in "Knife" in the search box... it should show all the rows with the word "Knife" in it.

The Search Table is within column B:L and is continuously updated.

Your help is greatly appreciated.

[Bernie Deitrick]

The easiest way is to use a column of formulas, like this in M2, with 'knife' in M1:

=NOT(ISERROR(MATCH("*" &$M$1&"*",B2:L2,FALSE)))

Then copy down and filter on column M to show TRUE values. You can use a macro to do all that but it is just as easy to enter the value into M1 and use Data filters.

[davesexcel]

Here is a thread on autofilter as you type and a webpage
-
AutoFiler as you type thread
-
http://www.xlorate.com/excel-questio...s%20you%20Type

-
I also have a horrible vid, I think I will make a new one, hard to hear anything
My Vid

[bhenlee]

Thanks for the thread davesexcel.. Really informative.. unfortunately i can't seem to make it work with my own table..

what i would like to do it type in a text in the search box and excel will look into the entire table of data...

then it will display all rows that have the text/data in any of the cells in the row...

I've attached a sample worksheet.. what i would like to do is be able to search thru the entire table..so even if i type Blue the table will display rows with the word Blue

[xladept]

Your code adapted seems to work:

Please Login or Register  to view this content.

[davesexcel]

Sure thing,
Check it out.

Please Login or Register  to view this content.

[bhenlee]

Thank you, XLAdept unfortunately can't get your code to work..

What i'm looking for is the ability to filter the table by any search item. So even if i type in Ben or Blue or 27 in the search box - it would go thru the entire table and display the rows with the corresponding result.

[xladept]

Oh - my bad! But it looks like davesexcel has solved your problem!

[bhenlee]

yes xladept. thank you davesexcel.. few things can't seem to make your code work on my actual table

1) my table have 12 columns starting from B4 to M4 down.. there will be empty cells in the entire table.. cells in Column D & E, however will always have data

2) could i add a button that would clear the search box when click thereby clearing any filters applied

I notice that the code will cease to work if there are empty cells in the table.. how do i go around that?

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

[bhenlee]

here you go.. i've attached a sample of the worksheet i'm working on...

Here's what I'm trying to achieve:

1) Type any search item (Date or any item Code or any Product1 etc) in the search box and display all rows containing the search item

2) A code to clear the search box and return the table in its unfiltered state

[Bernie Deitrick]

Filtering columns does not use OR logic - it uses AND logic. If you want to find all values but in any column, you need to use a helper column for formulas as I posted, or use code to fill a single column with TRUE / FALSE values based on the values in that row.

[bhenlee]

Hi Bernie,

What I'm trying to say any search item like Boxes or 10% or Gloves or 1234 in the search box... the code will go thru the entire table and display the rows which contains say Boxes...

[Bernie Deitrick]

[bhenlee]

Thanks Bernie but it's not working.. is there anyway the code can be used with the search box instead?

[Bernie Deitrick]

What do you mean by "it's not working"? Clear all the filters from your sheet and try again - I have assumed that column M is free so make sure you insert a new column M if that is not the case

[bhenlee]

It does not work as intended.. when i run the code and type in the search item and click ok... all rows with data are filtered..

also is there any way we could use a search box instead?

[Bernie Deitrick]

What rows do you want filtered? You just said columns B to L without specifying rows: "The Search Table is within column B:L and is continuously updated."

[davesexcel]

here you go.. i've attached a sample of the worksheet i'm working on...

Here's what I'm trying to achieve:

1) Type any search item (Date or any item Code or any Product1 etc) in the search box and display all rows containing the search item

2) A code to clear the search box and return the table in its unfiltered state

Is this not the same sample you supplied before?

[xladept]

Try:

Please Login or Register  to view this content.

[bhenlee]

Thank you xladept and davesexcel. not sure if I'm doing it correctly but i can't seem to get your code to work on the attached file. Your code seems to work on the initial file that i uploaded (only 3 columns).

One thing about your code - it doesn't work if i enter a number in the search box.

Really appreciate your time and help guys.

[bhenlee]

Hi Davesexcel - It's a different file just the same filename.. the new file should have columns B:L

[davesexcel]

This seems to work, it will not work for the percentage columns though, possibly a different algorithm would be required.
I commented out the hide arrow lines as it is not important for the exercise.

Please Login or Register  to view this content.

[bhenlee]

Thanks Davesexcel.. seems to be in the right direction.. still a few bugs..

1) when i type in the search box - the filter arrows are moved to row 1 and the column titles are hidden... it only shows the row with the text that was searched for.


2) I noticed that the code already works if i typed in only part of the number and not the entire number (ex. 1234...) but when text is entered it will not show the result until the entire word is entered as it is shown in the table.. is it possible to do it like even if i just type the letter b it will show results for Bags and Boxes?

I really appreciate all your time and help..

[xladept]

Another attempt:

Please Login or Register  to view this content.

[CK76]

I had done something similar in another forum. This is using Table.

See if you can adopt this.

Please Login or Register  to view this content.

[bhenlee]

Thank you xladept....

Was able to figure things out and your code works perfectly...

Thank you so much for your time and help!!!

[xladept]

Are your headers in the first row? If so the newest code should work - it works on the second sample you posted

(I edited that code a half hour after the original post.)

And - thanks for the rep!

[bhenlee]

Yes everything is working as planned

the rep is just a small token of my appreciation...

Thank you so much..

[xladept]

You're welcome!

[bhenlee]

xladept if it isn't asking too much... how do i make your code work on a protected worksheet?

thank you

[xladept]

Maybe:

Please Login or Register  to view this content.

*You'll need to change the password to the actual password (in red).

[CK76]

Or you could allow filters when you protect sheet.
And unlock cell linked to textbox.

[ksj1204]

This one works well.

[xladept]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.