Search box in Excel to search key words in sentences in multiple worksheets

**fernandoii676** · 10-19-2012, 11:04 AM

Afternoon,

I've used this website a lot as it has helped resolve so many questions, so thank you everyone. There is one question that I havent found an answer to. I know there is someone out there who has the answer.

I've attached an example excel file Example FI.xlsx, this spreadsheet will be a database which contains all the questions/answers I have encountered from various documents/files/reports/etc. The database will be updated regularly with new questions/answers into the different worksheets depending on category.

Each worksheet will have:
Column A - will have the name of document/file/report.
Column B - would contain the question or answer

In "Search" sheet, I would like to use a search box, to type a key word that I would like to find across the different worksheets. The search would look into the Column B, through the phrase/paragraphs/sentences (number of words and length in Column B will vary). And then provide in search sheet, the worksheet that contains the word (Column F), the document the word came from (Column G) and the row it is found in (Column H).

I would like to add extra questions/answers to the different worksheets and even add new ones. So it would be very helpeful to understand what formula/macro changes I need to complete to update Excel document.

So anyone who is able to help, please let me know what I need to do.

Thank you again for all your posts and help

Cross Post: http://www.ozgrid.com/forum/showthread.php?t=170995

**mike7952** · 10-19-2012, 12:01 PM

Give this a try. Type your Search term into cell B3 then run macro

Please Login or Register  to view this content.

**fernandoii676** · 11-01-2012, 08:12 AM

Hi Mike7952,

Thank you so much for your response. Apologies for delay in reply, been away on leave.

How do I run a macro?

Kind regards

**mike7952** · 11-01-2012, 10:18 AM

Give this a try Example FI.xlsm

**fernandoii676** · 11-05-2012, 01:27 PM

Thank you so much Mike,

Amazing. Pure class.

So the macro searches all the tabs in that documents? If I wanted to add more tabs, do I need to edit the macro? If so how do I do it so I don't have to disturb you.

Also, if I change the name of the tabs, will that effect the macros?

Once again, thank you so much for your help.

**mike7952** · 11-05-2012, 01:33 PM

No need to do anything if you add more tabs as long as there in the same format as the ones in your sample file.
If you change the name of the output sheet then just change in the code at the top were the comment is

**fernandoii676** · 11-05-2012, 01:42 PM

Fantastic

Once again, thank you so so much.

Now I will start populating the data sheets.

I need to go on Microsoft Excel training courses to learn about macros.

Thanks Mike