Search Across multiple worksheets

[jjward101]

Hello! I hope you are all safe and not breathing near, well, anything.
I am using the extra time on my hands right now to try to update some basic spreadsheets I use for work,
with the problem being that I only have VERY basic excel skills. I am hoping to both accomplish a few things
as well as to learn.

The attached spreadsheet contains worksheets for each category of part that I use. The 'Main Index' page,
has an area at the top that i would like to use as a quick search tool, using the first four digits of our part numbers.
(Beneath that, I am going to use as a table of contents to go to a specific sheet using hyperlinks).

Can anyone point me in the right direction of how to accomplish the multi-sheet search? I would like to return
results for all parts using the same first 4 digits.

Thanks in advance for any help.

[MarvinP]

Hi jj,

You can use the Find and Replace dialog for your problem. Click on Ctrl+F to bring up the Find dialog box. Then click on Options and change it to WorkBook instead of WorkSheet. Then Change Formulas to Values.

Now type in something to find like "Phone" and click on the Find All button. It will show you any cell in the whole workbook with "phone" in it. You can double click on any of the lower rows and jump to that location.

NOW - you have a problem looking for "4100" because it is a number and not TEXT. I see your 4100 parts are the first part of many strings so instead of "4100" type "4100-" and click the Find All button.

I think this is a much better way to find stuff across all sheets. You don't even need to type in the first 4 letters as it will find the term down in the string also. Hint: Click on the top row of the Find All and it will sort by that column..

[jjward101]

Thanks, I had thought of that, but would really like to have it as more of a form, as we are going to put it on our server for the entire office to use, and repeatedly telling people how to use Ctrl F will get old very, very quickly.

[MarvinP]

Hi jj,

I had this same question a few days ago. The OP (original poster) wanted to put all the results on a single master sheet, kind of like you want. As soon as you get into VBA code the problem magnifies by people wanting more and different features. Ctrl+F is a standard in all Microsoft Office products and might help them with other products. I'm just trying to make your life easier.

[alansidman]

Here is a link to the thread that Marvin was referring to if you wish to look--->https://www.excelforum.com/excel-for...ll-sheets.html

[jjward101]

I hear what you are saying, but this is a bit different. The only use will be to look up the part number. ALL other aspects of what we do from there will be used in an entirely different piece of software.