Here's another way using the same basic technique...

Create these named ranges:

InsertNameDefine
Name: Part
Refers to: ='Master List'!$A$2:$A$6

Name: Qty
Refers to: ='Master List'!$C$2:$C$6

Name: Status
Refers to: ='Master List'!$F$2:$F$6

On the sheet where you want the results:

A1 = manufactured or purchased or obsolete
I'd use a drop down list for this.

How to setup a data validation drop down list:

http://youtube.com/watch?v=t2OsWJijrOM

B1 = a formula that returns the count of records

=COUNTIF(Status,A1)

C1 = Enter this array formula** to extract the part numbers

=IF(ROWS(C$1:C1)<=B$1,INDEX(Part,SMALL(IF(Status=A $1,ROW(Part)),ROWS(C$1:C1))-MIN(ROW(Part))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

You need to drag copy down the column to a number of cells that is at least
equal to the maximum count for the "status". For example, if obsolete
appears the most times in your master list, say 20 times, then you need to
copy the formula to at least 20 rows.

Assuming that the part numbers are unique...

D1 = formula to return the Qty

=IF(C1="","",SUMIF(Part,C1,Qty))

Copy down to the same number of rows as you copied the formula in cell C1.

--
Biff
Microsoft Excel MVP


"buscher75" wrote in message
...
I have spreadsheet that holds all the information needed. The value in one
column must be one of three words. Manufactured, Purchased, Obsolete. I
would like to pull a list in a new tab of all "Manufactured" parts. I do
not
need the entire row, just the cell values of a few columns. So for
example:
Master List (sheet 1):
clm"A" clm"C" clm"F"
Part1 qty manufactured
Part3 qty purchased
Part4 qty obsolete
Part5 qty purchased
Part6 qty manufactured

If I enter "manufactured" in to A1 on sheet 2, part1 and part6 will
fill-in
in the rows below. I would also want the qty to fill in those rows as
well.
Hope this makes sense!

Thanks in advance.