If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filter and display rows on another worksheet
Thanks Frank,
Is there a way to automate the "Advanced Filter" to work like a function? I want to be able to enter the criteria in a cell and have the filter automatically calculate all of the time. Thanks "Frank Kabel" wrote: Hi sorry, my fault. Try using 'Data - Filter - Advanced Filter for this or have a look at: http://www.rondebruin.nl/copy5.htm -- Regards Frank Kabel Frankfurt, Germany JML wrote: Thanks Frank, The issue is that I don't want to summarize any of the data, but the data area of the pivot table forces me to summarize (count, add, etc.) the data. I want all of the actual values for the criteria I specify to appear. Thanks "Frank Kabel" wrote: Hi use a pivot table for this. See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany wrote: Hello, I would like help filtering a range of data and displaying it on another worksheet based on criteria listed in the 2nd worksheet. Ex. (Worksheet #1) Column(A) Column(B) Row(1) Job# amount Row(2) 1001 1 Row(3) 1002 2 Row(4) 1002 3 Row(5) 1001 4 (Worksheet #2) - This is the result I am looking for. Column(A) Column(B) Row(1) Criteria= 1001 Row(2) job# amount Row(3) 1001 1 Row(4) 1001 4 Thanks in advance for the help!! Josh |
#2
|
|||
|
|||
Filter and display rows on another worksheet
Hi
if you want to do this automatically you can do this with some array formulas. e.g. try the following formulas on sheet2: A3: a non array formula =IF(B3"",$B$1,"") B3: enter the array formula (entered with CTRL+SHIFT+ENTER) =IF(ISERROR(INDEX('sheet1'!$B$1:$B$100,SMALL(IF('s heet1'!$A$2:$A$100=$B $1,ROW('sheet1'!$A$2:$A$100)),ROW(1:1)))),"",INDEX ('sheet1'!$B$1:$B$100 ,SMALL(IF('sheet1'!$A$2:$A$100=$B$1,ROW('sheet1'!$ A$2:$A$100)),ROW(1:1) ))) copy both formulas down as far as needed -- Regards Frank Kabel Frankfurt, Germany JML wrote: Thanks Frank, Is there a way to automate the "Advanced Filter" to work like a function? I want to be able to enter the criteria in a cell and have the filter automatically calculate all of the time. Thanks "Frank Kabel" wrote: Hi sorry, my fault. Try using 'Data - Filter - Advanced Filter for this or have a look at: http://www.rondebruin.nl/copy5.htm -- Regards Frank Kabel Frankfurt, Germany JML wrote: Thanks Frank, The issue is that I don't want to summarize any of the data, but the data area of the pivot table forces me to summarize (count, add, etc.) the data. I want all of the actual values for the criteria I specify to appear. Thanks "Frank Kabel" wrote: Hi use a pivot table for this. See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany wrote: Hello, I would like help filtering a range of data and displaying it on another worksheet based on criteria listed in the 2nd worksheet. Ex. (Worksheet #1) Column(A) Column(B) Row(1) Job# amount Row(2) 1001 1 Row(3) 1002 2 Row(4) 1002 3 Row(5) 1001 4 (Worksheet #2) - This is the result I am looking for. Column(A) Column(B) Row(1) Criteria= 1001 Row(2) job# amount Row(3) 1001 1 Row(4) 1001 4 Thanks in advance for the help!! Josh |
#3
|
|||
|
|||
Filter and display rows on another worksheet
i have this piece of code that i got from a file that i downloaded from
a website (lacher). its on a sheet which has 4 columns. there are 2 input parameters which define the filtering. it could be tweaked around a little bit and used for your purpose. i am just too lazy to try it for your example right now. let me know if help is needed with tweaking. mac. *** Sub Filter_Test() '\ update criteria values in hidden worksheet With Worksheets("CriteriaValues").Range("Database") .Cells(2, 1).Value = InputBox(prompt:="Enter Year for Filter") .Cells(2, 2).Value = InputBox(prompt:="Enter Month for Filter") End With '\ use hidden criteria range to filter data on datavalues worksheet Worksheets("DataValues").Range("Database").Advance dFilter Action:=xlFilterInPlace, CriteriaRange _ :=Worksheets("CriteriaValues").Range("Database"), Unique:=False End Sub --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Filter and display rows on another worksheet
The function I mentioned in the other thread you started on this same
topic can be used that way. Alan Beban JML wrote: Thanks Frank, Is there a way to automate the "Advanced Filter" to work like a function? I want to be able to enter the criteria in a cell and have the filter automatically calculate all of the time. Thanks "Frank Kabel" wrote: Hi sorry, my fault. Try using 'Data - Filter - Advanced Filter for this or have a look at: http://www.rondebruin.nl/copy5.htm -- Regards Frank Kabel Frankfurt, Germany JML wrote: Thanks Frank, The issue is that I don't want to summarize any of the data, but the data area of the pivot table forces me to summarize (count, add, etc.) the data. I want all of the actual values for the criteria I specify to appear. Thanks "Frank Kabel" wrote: Hi use a pivot table for this. See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany wrote: Hello, I would like help filtering a range of data and displaying it on another worksheet based on criteria listed in the 2nd worksheet. Ex. (Worksheet #1) Column(A) Column(B) Row(1) Job# amount Row(2) 1001 1 Row(3) 1002 2 Row(4) 1002 3 Row(5) 1001 4 (Worksheet #2) - This is the result I am looking for. Column(A) Column(B) Row(1) Criteria= 1001 Row(2) job# amount Row(3) 1001 1 Row(4) 1001 4 Thanks in advance for the help!! Josh |
Thread Tools | |
Display Modes | |
|
|