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