The specific rows where your data resides is important - these were written for data starting in row
2:

ROW('Master List'!$F$7:$F$34)-1)
needs to be changed to
ROW('Master List'!$F$7:$F$34)-6)

or

ROW('Master List'!$F$7:$F$34)-(ROW('Master List'!$F$7)-1))

HTH,
Bernie
MS Excel MVP


"buscher75" wrote in message
...
I plugged in your formulas and at first glance, they work. However, I
noticed it is pulling in part numbers that are not "manufactured" and after
so many lines, the formula produces "#REF" results and eventually blank
results. Any thoughts as to what I might have done wrong? I did alter the
referenced cell values to reflect the correct cells. Here is a copy of the
formula for cell B2. I stop at $F$34 because I am still designing master
list.

=IF(COUNTIF('Master List'!$F$7:$F$34,$A$2)=ROW()-ROW($B$1),INDEX('Master
List'!$A$7:$A$34, LARGE(('Master List'!$F$7:$F$34=$A$2)*(ROW('Master
List'!$F$7:$F$34)-1),COUNTIF('Master
List'!$F$7:$F$34,$A$2)-ROW()+ROW($B$2))),"")

Thank you for helping me. I would NEVER have been able to figure this out
on my own. I really appreciate it.


"Bernie Deitrick" wrote:

buscher75,

In cell A2, put the value

Manufactured
In Cell B2, array enter (enter using Ctrl-Shift-Enter) the formula

=IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$A$2:$A$20,
LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master
List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"")

In Cell C2, array enter (enter using Ctrl-Shift-Enter) the formula
=IF(COUNTIF('Master List'!$F$2:$F$20,$A$2)=ROW()-ROW($B$1),INDEX('Master List'!$C$2:$C$20,
LARGE(('Master List'!$F$2:$F$20=$A$2)*(ROW('Master List'!$F$2:$F$20)-1),COUNTIF('Master
List'!$F$2:$F$20,$A$2)-ROW()+ROW($B$2))),"")

Change all instances of $20 to $ and a number that is at least as high as the row number of the
end
of your data set on Master List. Then copy B2:C2 and paste down until you start getting blank
values.

Then you can change A2 to purchased to get a list of the purchased parts. or obsolete......


HTH,
Bernie
MS 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.