Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
very urgent (advance filter)
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
very urgent (advance filter)
-- Hth Kassie Kasselman Change xxx to hotmail "RKS" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
very urgent (advance filter)
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 wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
very urgent (advance filter)
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" wrote: 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 wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
very urgent (advance filter)
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 wrote: 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" wrote: 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 wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
very urgent (advance filter)
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("Crite ria"), _ 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 "Debra Dalgleish" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advance filter question | Excel Discussion (Misc queries) | |||
Advance Filter Formula | Excel Discussion (Misc queries) | |||
Advance Filter | Excel Discussion (Misc queries) | |||
advance filter | Excel Discussion (Misc queries) | |||
about advance filter | New Users to Excel |