Search for value through entire workbook

I have read this forum and nothing seems to fully apply to what I am trying to do. I just want a search cell, type in a value such as 'Tower' and have it query up to 9 additional sheets within the workbook. I am trying to find costcodes for multiple 'Tower' Options.


Search ---> INPUT TEXT

Output ---> Everything found
---> Additional
---> So on and so on.

What do you want the results to look like? In the example you gave, I found 118 cells with "TOWER".

It might be possible to do this with a complex formula but I would be more inclined to use a VBA solution. Are you open to using VBA? Any experience with it?

Hi

Some examples would have been useful.
Which are the 9 sheets? Are they the ones that all have the same layouts with columns A:F and is it the Description column you're searching?

Personally I'd simplify your data and put all your data on a common sheet to create a single database, adding an extra column to record the description that the current tab name shows.

You'll then have a much more efficient database which will allow you to interrogate it in much moe efficient ways. You'll also have the big advantage of being able to use a Pivot Table for analysis and summarisation.

The proj code and or description, both would be nice. I believe "TOWER*" should be in the code as well.

used to have pretty good experience with vba, I am pretty rusty nowadays though, definitely not against it.

Enter all the names of the worksheets within your workbook somewhere in your workssheet or another worksheet and refer to it with this formula:

Formula:

Please Login or Register  to view this content.

If you do a global search you'll get 118. The formula gives you 117 because it is not counting the one in the LOOKUP sheet.

Originally Posted by 6StringJazzer

Nice solution.

jclegg42002, do you need a list of what was found, or just the count?

A list of what is found, and description would be great, not count.

I did as you asked..... about adding the sheetnames

Here is a revised copy of what I am looking for... Thanks for all of your help.

Originally Posted by 6StringJazzer

So you want to search for the term only in the "Description" columns, and then return "Item" and "Description" in the list?

Exactly, well if I can see how you guys get it working on the first few I can modify to do the rest I think.

I'm struggling to understand.

You seem to have a partial database in columns J:L on the lookup sheet and this is where your 5 'Tower' results seem to have come from. So I'm not quite sure why you are making it harder by trying to search across nine sheets.

As I said in #3 these things are much simpler of you have a single database. One you have that then a simple Advanced Data Filter will extract all the information you're looking fif and you won't need any formula. Even easier yuo'll be abe to apply A Pivot Table and see the information needed without even needing to perform a data filter to extract the records form the database.

So I suggest you extend that database on the lookup sheet, or better still put it in a sheet of its own and work from that.