How to search multiple worksheets and put results in a seperate worksheet.

**bmsjeff** · 09-21-2009, 08:09 AM

I have an Excel workbook that has 3 worksheets in it:
Parts1, Parts2, Parts3
The columns are:
Vendor, Part Number, Cost, Retail, Quantity, Location

I want a 4th. worksheet that I can enter a part number and it will search through the 3 worksheets and display the results in worksheet 4.
It is possible for their to be multiple results for the same Part Number, but not always. (For example, the same part number can be in multiple Bin Locations.)

I am a newbie at this, so detailed instructions would be appreciated.

I have tried using Vlookup, but it does not seem to do what I am looking for.

**NBVC** · 09-21-2009, 08:17 AM

Post a sample workbook.. with some parts and how you want the 4th sheet to look.

**bmsjeff** · 09-21-2009, 08:34 AM

Here is the workbook. Parts1 and Parts2 two both are using all 65000 rows.

**NBVC** · 09-21-2009, 09:04 AM

See attached for formula based approach.

In each Parts sheet I added a helper column to count matches to Part number in the Search sheet.

Formula in Z2 of each sheet is

Please Login or Register  to view this content.

copied down

In each Part sheet after the first, at the top of the Z column is a formula to find the Max number from the previous page to add number of matches in current page on to...

So in Z1 of PART2 and PART3, there is formula:

=MAX(PARTS1!Z:Z) and =MAX(PARTS2!Z:Z), respectively.

Then in the Search page there is a count formula in D1 that shows the maximum number on the last Part3 page...

=MAX(PARTS3!Z:Z)

Then, to extract the data, we have formula in A6 of Search page:

Please Login or Register  to view this content.

copied down as far as you need and across all required columns.

**jimbogey** · 11-12-2010, 06:30 PM

I am looking for something that is very similar to this solution, but I need to be able to search an indefinite number of worksheets.

I am new to this , but I am trying my hand at scripting because I do not think the formula approach will work for my problem.

Basically I have multiple workbooks that need to be searched and displayed. I was able to run a script that consolidates all workbooks into on each having their own worksheet.

I now need to figure out how I can search each of these sheets. The first issue is that the number of worksheets and their names may be different each time I run the consolidation script.

How can I create a script that will perform behave the same as the formula used in this solution but search an indefinite number of sheets with unknown names.

Thanks
Jim

**NBVC** · 11-13-2010, 09:45 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.