Filter and show data based on a reference cell

[Guest]

I have Worksheet 2 with a lot of data. I need to have an input cell on WS1
where I can input serch criteria ("text") as needed and it will then show the
results on WS1.

Any help would be appreciated!

 

[Debra Dalgleish]

You can do this with an Advanced Filter, manually or programmatically.
There are examples here:

http://www.contextures.com/excelfiles.html

Under 'Filters', look for "FL0002 - Product List by Week Number" and
"FL0005 - Phone List for Selected Name"

 

[Guest]

Dear Bebra

Thanks. The only change i need to make to FL0005 is to be able to populate
the criteria in C3 from another cell. The reason is that the worksheet will
be under an Xcelsius file and I need to input the criteria at the Xcelsius
level which wll populate C3. Is this possible?

 

[Debra Dalgleish]

Perhaps you could change the cell so it links to the Xcelsius file, and
use the Worksheet_Calculate event instead of Worksheet_Change. For example:

'============================
Private Sub Worksheet_Calculate()
Application.EnableEvents = True
On Error GoTo errHandler
Dim wsD As Worksheet
Dim wsC As Worksheet
Set wsD = Worksheets("Data Entry")
Set wsC = Worksheets("Customers")
wsC.Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsD.Range("C2:C3"), _
CopyToRange:=wsD.Range("A66"), _
Unique:=False

Exit Sub

errHandler:
Application.EnableEvents = True
MsgBox "Names were not retrieved"

End Sub
'===============================