+ Reply to Thread
Results 1 to 4 of 4

returning entire row based on single or multiple criteria

  1. #1
    Registered User
    Join Date
    05-30-2008
    Posts
    5

    Question returning entire row based on single or multiple criteria

    On sheet 1 I have nine columns with identifying criteria that pertain to one object. On sheet 2 I want to label the columns as the same as sheet 1 so a user can type in the criteria to search for underneath the labeled columns. Some instances it may only return one row but could quite possibly return multiple rows. I don't know what function or how to set it up to make it work. Any help would be appreciated. I have attached a sameple workbook set up similiar to what I need it to be.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: returning entire row based on single or multiple criteria

    Look at the help function of the advanced filter. It does exactly that!
    http://office.microsoft.com/en-us/ex...001781033.aspx
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    An Advanced Filter approach to pulling data

    Here's an approach you might be interested in:
    (It's less complicated than it might look)

    Assumptions:
    Sheet1 contains source data in cells A1:B10 (EmpID's and Ages)
    Sheet2 is where you want the extracted data to be displayed

    Using Sheet2:
    A1: EmpID
    B1: Age
    From the Excel Main Menu:
    Please Login or Register  to view this content.
    I1: EmpID
    I2: 24

    Please Login or Register  to view this content.
    Still using Sheet2:
    Please Login or Register  to view this content.
    (Notice: you are on Sheet2, and creating a Sheet2-level range name, but
    the referenced range is on Sheet1)

    The reason: An advanced filter cannot SEND data to another sheet, but
    it can PULL data from another sheet.

    Now...set up the Advanced Data Filter:
    <Data><Filter><Advanced Filter>
    Select: Copy to another location
    List Range: (press F3 and select Database)
    Criteria Range: (press F3 and select Criteria)
    Copy To: (press F3 and select Extract)
    Click [OK]

    Note: if you want to run that Advanced Data Filter repeatedly,
    you'll need to re-select Database each time
    ....OR...if you're feeling a bit ambitious...

    You can build a simple macro to automatically re-run the filter:
    Press [Alt]+[F11] to open the VBA editor
    Right click on the VBA Project folder for your workbook
    Select: Insert>Module

    Then, copy/paste this code into that module:

    Please Login or Register  to view this content.
    To run the code:
    Please Login or Register  to view this content.
    To test, change the value of I2 and run it again.

    Is that something you can work with?
    Last edited by Ron Coderre; 10-22-2008 at 03:23 PM. Reason: add title
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    05-30-2008
    Posts
    5
    I tried what you posted about the advanced filter but cannot get it to work the way I would want it to. I'm trying to get it to work, so when you type in something different in the criteria cells. It returns the row or rows that match what your searching for on sheet1 in the results row below. So say I type in 15 for the dwg# in the search criteria. It would return the information for the dwg 15 in the results below, but if I would change the 15 to like 109 then it would either automatically do a search again and then return all information dealing with dwg 109. Maybe I need to put a button with a macro so when you enter the information you want. Then you would hit the button to return the information matching your criteria entered. The only problem is I have no experience with that. I attached an example which is how the information is setup and how I would want to enter the information above on sheet2 and it would return in under the column indetifiers beneath the search criteria on sheet2. I don't know if this is possible. Any help would be appreciated and thanks again for the information already. I don't think that is what I'm wanting but could be I'm not doing something right.

+ 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