Retrieve data based on a search word

[tnsvolley]

I am trying to create a searchable directory. Tab 1 is a the search page. Tab 2 holds all data
Idea is to type in a word in the search box (B2) and it searches Column A on Tab 2 and returns all rows with that contain that search word. I have removed phone numbers and some data from the form for privacy sake, but for instance you want to search for the word "tax"....it would give copy the rows from tab 2 and return them on tab 1. I would hide the data tab while this is being used so no one can accidentally erase/change anything.

Any help is appreciated!

FOPS Master Phone List- DATA removed.xlsx

[TheWrapperGuy]

A question, do you want the previous search data from tab 1 cleared when a new search starts?

[TheWrapperGuy]

The following can be added to your Search sheet macros in the IDE

Please Login or Register  to view this content.

[tnsvolley]

sorry, see response below

[tnsvolley]

The following can be added to your Search sheet macros in the IDE

Please Login or Register  to view this content.

Thank you...I do want it cleared when a new search starts...does the code above allow for that?

When I use the code, and clear out the search field, it displays everything from data tab...is there a way if search field is blank, that nothing displays??

[skywriter]

Here's another possibility using a different method.
Right click on the "Search" worksheet tab and choose view code and paste the code into the blank window.

Please Login or Register  to view this content.

[tnsvolley]

Here's another possibility using a different method.
Right click on the "Search" worksheet tab and choose view code and paste the code into the blank window.

Please Login or Register  to view this content.

Thank you, but if i clear out the search field, it does not clear out the past search data.

[TheWrapperGuy]

Try:

Please Login or Register  to view this content.

[skywriter]

Thank you, but if i clear out the search field, it does not clear out the past search data.

You sure that's what you want?

There's never a time when you want to search, keep the data and then search again with perhaps another term?

I would suggest a clear button using another macro.

[tnsvolley]

Try:

Please Login or Register  to view this content.

If i type in micro, i get three results. Then I clear the search field by hitting backspace, and it displays everything, looping, says Not Responding and then is blank again.

[tnsvolley]

You sure that's what you want?

There's never a time when you want to search, keep the data and then search again with perhaps another term?

I would suggest a clear button using another macro.

If I type in "micro" and get results, I see three. I would like to clear Search field by using delete or backspace so that when I close it and reopen, tab 1 is empty. As code is now, when I clear by hitting backspace, everything is displayed.

[TheWrapperGuy]

Instead of back space, try hitting the Del key and then press enter

[skywriter]

With this code you have to type a search term and hit enter.

If you choose the search box again and hit delete it will clear the old data.

If you choose the search box again and type another term the previous data will be preserved and some new data will come in below the old data.

Please Login or Register  to view this content.

[tnsvolley]

With this code you have to type a search term and hit enter.

If you choose the search box again and hit delete it will clear the old data.

If you choose the search box again and type another term the previous data will be preserved and some new data will come in below the old data.

Please Login or Register  to view this content.

Ok, sorry to be a pain...this does work with delete, but is there a way to search micro and then search civil, without having to delete?

[skywriter]

Ok, sorry to be a pain...this does work with delete, but is there a way to search micro and then search civil, without having to delete?

Yes, I explained that in the last paragraph.

You type micro hit enter, select the search box again and type civil and hit enter again.

Civil will replace micro by just selecting the box and typing. No need to backspace or hit delete or highlight the previous term, just click the box and start typing and hit enter.

[6StringJazzer]

tnsvolley, please do not hit "Reply With Quote" every time you add a post. It is not necessary quote posts unless there is something specific you are responding to. Even then just quote the relevant portion.



Rule #12. Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

[TheWrapperGuy]

I'm curious tnsvolley, have you tried my last code block? I've run it multiple ways from my end and I'm pretty sure it meets your requirements. I ask because you haven't said either way.