Excel - Filter data through a formula

Issue

I need a formula of the following quotation:

Column A Belongs To Customer Name

Column B Belongs The Date

Column C Belongs To Item

Column D Belongs To Amount

There Are Too Many Data Already Posted In These Columns..

& I Need To Filter These Data In Other Sheet With Using Formula, Not Using The By-Default Filter Option.

For Example:

Suppose This Is A Data

Sheet 1:

Customer Name Date Item Amount

Paul 01-Jan-10 HandSet 18000

Shaun 15-Jan-10 Charger 200

Rahul 30-Dec-09 Battery 600

Naeem 11-Jan-10 Handset 16000

Paul 14-Nov-09 Battery 250

Naeem 19-Jan-10 Battery 150

Paul 25-Dec-09 Handset 9000

Shaun 15-Jan-09 Charger 100

Rahul 18-May-09 Battery 250

Sheet 2:

If I Need To Filter My Data As Customer Name.. In A2 I Type The Customer Name Than In Sheet 3 Or In Same Sheet My Data Will Filtered As Customer Name, If I Need To Filter My Data As DateWise.. In B2 I Type The Date Than In Sheet 3 Or In Same Sheet My Data Will Filtered As DateWise, If I Need To Filter My Data As Itemwise, In C2 I Type Item Name Than In Sheet 3 Or In Same Sheet My Data Will Filtered As Itemwise..

But I Need To Filter My Data Through Formula, I Think This Solution Will Be Solved As Macro.....

Like I Need My Solution As Following:

Sheet2:

A2 Is Input Name Of Customer Name

B2 Is Input Value Of Date

C2 Is Input Name Of Item

D2 Is Input Value Of Amount Or Cost

A B C D

1 Customer Name Date Item Amount

2 If I Type Here Paul

Than It Go To Sheet 1 & Find The Paul's Entry & Display All Pauls Entry In Sheet 3

Sheet 3:

I Need This Data Through Formula

Customer Name Date Item Amount

Paul 01-Jan-10 HandSet 18000

Paul 14-Nov-09 Battery 250

Paul 25-Dec-09 Handset 9000

Solution

So here is the code:

Sub Test() Sheets("Sheet3").Rows("2:10").ClearContents Sheets("sheet1").Select Range("A2").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet2").Range("A2") Rows("2:10").Select Selection.Copy Sheets("Sheet3").Select Range("A2").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("A1").Select Application.CutCopyMode = False Selection.AutoFilter Sheets("Sheet3").Select Range("A2").Select End Sub

Since you gave 9 lines of example data, I used Rows("2:10") on line 2 and 7 in the code. Adjust the rows to the amount of data you have.

In case you don't know how to implement the code: Open VBE (ALT+F11), goto top menu's, insert>module and paste the code into the empty field.

Thanks to TrowaD for this tip.