returning entire row based on single or multiple criteria

**cdevericks** · 10-22-2008, 02:23 PM

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.

**rwgrietveld** · 10-22-2008, 03:21 PM

Look at the help function of the advanced filter. It does exactly that!
http://office.microsoft.com/en-us/ex...001781033.aspx

**Ron Coderre** · 10-22-2008, 03:22 PM

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:

<Insert><Name><Define>
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$B$1

I1: EmpID
I2: 24

<Insert><Name><Define>
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:

<Insert><Name><Define>
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$B$10

(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:

'---Start of Code-------
Option Explicit
Sub PullMatchingData()
Range("Sheet2!Database").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("Sheet2!Criteria"), _
CopyToRange:=Range("Sheet2!Extract"), _
Unique:=False
End Sub
'---End of Code-------

To run the code:

<Tools><Macro><Macros> (or press [Alt]+[F8])
Select and run: PullMatchingData

To test, change the value of I2 and run it again.

Is that something you can work with?

**cdevericks** · 10-23-2008, 09:43 AM

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.