reference cells on one tab by value of cell on other tab

[buscher75]

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.

 

[Bernie Deitrick]

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]

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

 

[Bernie Deitrick]

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

 

[T. Valko]

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

Create these named ranges:

Insert>Name>Define
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:

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.

 

[buscher75]

Thank you for the help. It works just like I need it to.