+ Reply to Thread
Results 1 to 8 of 8

Find same information within a workbook

  1. #1
    Registered User
    Join Date
    08-28-2018
    Location
    Duncan, OK
    MS-Off Ver
    2016
    Posts
    4

    Find same information within a workbook

    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.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,324

    Re: Find same information within a workbook

    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).
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    08-28-2018
    Location
    Duncan, OK
    MS-Off Ver
    2016
    Posts
    4

    Re: Find same information within a workbook

    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.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,324

    Re: Find same information within a workbook

    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.

  5. #5
    Registered User
    Join Date
    08-28-2018
    Location
    Duncan, OK
    MS-Off Ver
    2016
    Posts
    4

    Re: Find same information within a workbook

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

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,324

    Re: Find same information within a workbook

    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
    Last edited by CK76; 08-28-2018 at 02:19 PM.

  7. #7
    Registered User
    Join Date
    08-28-2018
    Location
    Duncan, OK
    MS-Off Ver
    2016
    Posts
    4

    Re: Find same information within a workbook

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

  8. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,324

    Re: Find same information within a workbook

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1