filter/sorting

[ADEYADEEL]

I have sheet 1 with bill of materiel (product & material).
on 2nd sheet i have only material list with available stock(use in sheet 1 products).
i want separate cell on sheet 2 ,where i type/ select products and its show only material use in that product.

 

[awoohaw]

You can try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Sheet 1				Sheet 2			Type Product:	Product B
2	Products	Material			Material	Stock
3	Product A	Mat A			Mat A	6		Mat B	7
4	Product A	Mat B			Mat B	7		Mat A	6
5	Product A	Mat C			Mat C	6		Mat Y	9
6					Mat Y	9
7	Product B	Mat B
8	Product B	Mat A
9	Product B	Mat Y
ADEYADEEL

Cell Formulas
Range	Formula
H3:H5	H3	=INDEX($A$3:$B$9,MATCH($I$1,$A$3:$A$9,0),2):INDEX($A$3:$B$9,MATCH($I$1,$A$3:$A$9,0)+COUNTIF($A$3:$A$9,$I$1)-1,2)
I3:I5	I3	=IF(COUNTIF($A$3:$A$9,$I$1)>=ROWS(I$2:I2),VLOOKUP(H3,$E$3:$F$6,2,0),"")
Dynamic array formulas.