very urgent (advance filter)

[RKS]

Hi all,
I have an protected excel file (sheet1) with approximately 200 data entry
(200 rows and 10 columns). and user can enter the data in sheet1. I would
like to build a second sheet to do advance filter and display selected 5
column only not all but with a criteria cell in the second sheet. Criteria
are 2 or 3.say for example I have a invoice number column with different
invoice numbers. In the second sheet, I have cell A1, a2 and a3 as an input
cell... when I input on of the invoice number and other into cell A1,A2 and
A3 it would filter the 200 data and only display the 10 columns of
information of the invoice number in cell A1. If I change the invoice number
in cell A1, it would change accordingly. Do you think there is a way to build
this in excel?

Example: In Sheet1:=

Invoice No. Date Amt XX YY Shipdt Mode
1 100 01/01/08 50000.00 FF yy 05/01/08 ship
2

In Sheet2 : Which I want

Criteria : Invoice No, Shipdt mode

display column : Invoice No. Amt Shipdt Mode

Thanks in advance.
RKS

 

[Kassie]

--
Hth

Kassie Kasselman
Change xxx to hotmail

 

[Debra Dalgleish]

There's a sample file here that uses programming to run an advanced
filter after a number is typed in a cell:

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

Under Filters, look for 'FL0002 - Product List by Week Number'
You could change the code slightly, to adapt it to your workbook.
Instead of including all the table headings in the extract area, just
display the headings for the 5 columns you want.

 

[RKS]

Thanks Debra for reply. I can see FL0002 file. Its ok but my problem is that
i wants selected column in summary sheet like only 2 column. but in example
pick all column from data sheet. please tell me what i can change in code so
pickup only those column which i wants. rest is match my requirement. problem
only column. i can show u thru example.
data sheet A B C D E F D H column
summary sheet(result shet) I wants only A C F column.

CopyToRange:=Range("A6:g6"), Unique:=False its show all coulmn which i dont
need.

RKS

 

[Debra Dalgleish]

Instead of including all the column headings in the extract area, just
display the headings for the A, C and F columns.
Then, change the code so it refers to those headings, e.g.
CopyToRange:=Range("A6:C6"), Unique:=False

 

[RKS]

Please tell me how can we change code. i can show u what code i can use.
please change in my code for A,C and F column. waiting your reply please
change as earliest.

Code :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 3 Then
'calculate criteria cell in case calculation mode is manual
Sheets("ProductsList").Range("Criteria").Calculate
Worksheets("ProductsList").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("ProductsList").Range("Criteria"), _
CopyToRange:=Range("A6:g6"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("Data Entry").Range("D2").Calculate
End If
End Sub
================
Thanks for your co-operation.
RKS