ExcelBanter - Search engine in excel

Try this:
Here's an approach to try:
Assumptions:
On Sheet1 contains your data in cells A1:B10
On Sheet2 is where you want the extracted data to be displayed

Using Sheet2:
A1: EmpID
B1: Age

InsertNameDefine
Names in workbook: Sheet2!rngDest
Refers to: =Sheet2!$A$1:$B$1

I1: EmpID
I2: 24

InsertNameDefine
Names in workbook: Sheet2!rngCriteria
Refers to: =Sheet2!$I$1:$I$2

Next...still on Sheet2:
InsertNameDefine
Names in workbook: Sheet2!rngSource
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:
DataFilterAdvanced Data Filter
Select: Copy to another location
List Range: (press F3 and select rngSource)
Criteria Range: (press F3 and select rngCriteria)
Copy To: (press F3 and select rngDest)
Click [OK]

Note: if you want to run that Advanced Data Filter repeatedly, you'll need
to re-select rngSource each time....OR...

You can build a simple macro to automatically re-run the filter:
In a general vba module, enter this code:

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

To run the code:
ToolsMacroMacros (or [Alt]+[F8])
Select and run: PullMatchingData

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

Does that help?

***********
Regards,
Ron

"Howard" wrote:

I have a list of employee on one worksheet and on the second worksheet
worksheet I created a search engine ... but i need to display the information
of the result of the search on the second worksheet. I need the result to say
display all the employee that are 24 years old ....

How do i do that plz help