Find same information within a workbook

[Randy56]

I am working with a workbook that has over 300 individual sheets. I would like to know the easiest way to create something that will let me enter a particular item and then have it search the entire workbook. I would like to populate a separate sheet with the top line of each sheet when my search item is found on that sheet.

[CK76]

Use CTRL + F, and set Within: to Workbook. It will list all cells/sheets that match criteria.

Though I'd recommend reducing amount of sheets used in the workbook (I don't recommend more than 20 or so, and I typically don't use more than 10).

[Randy56]

See if I can make this clearer. I have a work book in Excel 2016. It contains 200+ sheets. Each sheet is a part number. That part number is always in cell A1 on each sheet. Column A of each sheet contains tool numbers that relate to the part number in A1. I want to be able to create something that will allow me to search column A of the entire workbook with a tool number and if it is found in a sheet to copy and paste the part number in A1 to a sheet that is labeled with the tool number.

[CK76]

I'd recommend creating single "Flat" data table to perform lookup operation, rather than using what you describe.

However, it can be done with your set up.
Please upload sample (with around 5 sheets), demonstrating your desired output. Clearly indicating where the data is inputted and where the out put goes.
It'll be much easier to help you with specific method then.

[Randy56]

Here is the example file. Not sure if it attached or not.

[CK76]

There are several ways to do what you are looking for.

1. PowerQuery - This option is easy to manage as it has GUI. However, you'll need to alter set up a bit.
2. VBA - As long as there are no duplicate this is probably fastest method, and relatively easy to manage.

You have more than one part number that match the tool# you provided. Should it return the first match? Or do you need all matches?

Process will significantly change depending on what result you are after.

Example UDF when only the first match is needed:

Please Login or Register  to view this content.

Sample usage:
In any cell in "DDJNR 16-4D" sheet:
=PartNum(TRIM(RIGHT(SUBSTITUTE(CELL("filename"),"]",REPT(" ",50)),50)))

Will return "PART THREE".
0.JPG

[Randy56]

Sorry for not getting back to you on this. I do need it to find all of the matches not just the first one.

[CK76]

Then UDF is not recommended.

Here's sample code. Assuming Tool# only appears once in each sheet.

Please Login or Register  to view this content.