+ Reply to Thread
Results 1 to 6 of 6

Search Function with multiple sheets

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Search Function with multiple sheets

    Hi All,
    I've had experience with doing some VBA, but very basic stuff...and searching usually isn't a problem but this is more complicated then usual, when dealing with multiple sheets and combining data.
    I can find one match and display it no problem...but finding all matches and displaying them is being picky.
    I have 3 sheets with mostly unique data in each, they can be cross-referenced with 1 column in each sheet(reference#).
    I want to create search page to search any of 3 or 4 columns, strings or date...then display the results below it.
    all of the search criteria is in Sheet1, so not too bad...but I want to see all results relating to that search from each sheet, and its gets tricky because any of the records could have 1 result, or multiple results(records/rows).
    I just want to display all results below the search, nothing too fancy, just he results from each sheet, since they all have different data.
    I'm going to make the results more pretty into a userform later...but if you have advice, I'm all ears...
    thanks in advance..for the help..(see attached for example)

    Data_Search.xlsm

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Search Function with multiple sheets

    Do you want to be able to search on any parameter (Ref#, LastName, FirstName, Date) and get the results or do you need to have all parameters entered.
    Can you resubmit a sheet with data in it for testing purposes.
    If you are going to do up a userform later the code for that will look a bit different than the code for your existing sheet.

  3. #3
    Registered User
    Join Date
    08-22-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Search Function with multiple sheets

    any of the parameters can be used...but not all.

    here is sheet with data.

    Data_Search.xlsm

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Search Function with multiple sheets

    I elected to use .Find rather than AutoFilter since I couldn't see the consistency amongst the sheets. Also, the search system is still not clear to me so I made a couple of guesses(assumptions). The code worked when tested. I will once again point out that UserForm code with be different.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-22-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Search Function with multiple sheets

    thanks for quick response stnkynts..sorry for delay.
    I noticed, with the array search, it will look for any of the search terms, even if two or three are used.
    if someone enters mutliple search terms, it needs to narrow down the search. if you know what i mean.

    THanks

  6. #6
    Registered User
    Join Date
    08-22-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Search Function with multiple sheets

    Here's another thing i thought about...
    Having 2 searches...
    the first one will search by any of the three header criteria, Name, Date, or both to have it more accurate.
    this search only searches SHeet1 for the data, and returns the records matched, and more importantly, the reference# coolumn

    then the user can use the second search to search by reference column and get the data from all sheets.
    The userform design is changed, you can see it on attached..

    let me know what the best way to approach that is?

    thanks for help
    Data_Search.xlsm

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Search function across multiple work sheets
    By evgn12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2014, 11:46 AM
  2. LOOKUP Array & Index Function (Search data across multiple sheets)
    By dizzle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2013, 09:26 AM
  3. Running a search function on multiple sheets
    By sjlouis in forum Excel General
    Replies: 1
    Last Post: 01-19-2012, 03:47 PM
  4. Need to Search for Value Across Multiple Sheets, Value Occur Multiple Times on Sheets
    By davidbriansmith in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2011, 12:31 PM
  5. Search and Delete Across Multiple Sheets According to Values in Sheets
    By Tyler_Durden in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-12-2009, 04:40 PM

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