Auto Populate rows based on criteria.

[rshahi]

Hi All,

I'm trying to figure out a formula to auto populate the rows from different worksheet if certain criteria matches. i have a file that has 4 worksheets ( floor, (List, TW and PW are where data is). I'm trying to auto populate the rows from list to floor if the location matches but can't seen to get it.. I want it to copy (Product code) colum from list to product code colum on floor sheet. And based on that value it will retract sales values from TW and PW using Vlookup. Sorry for my explanation. I've attached some screenshot of what i'm trying to achieve. I've added the example worksheet to show what i"m trying to achieve. Sorry I'm not really good at Excel. Thanks in Advance.

 

[etaf]

to extract the list use
=IFERROR(INDEX(List!$B$2:$B$100,SMALL(IF(List!$A$2:$A$100=1,ROW(List!$A$2:$A$100)-ROW(List!$A$2)+1),ROWS(A$2:A2))),"")
The Equal =1 change to 2 , 3 or 4
Or you could reference the 1,2,3,4 in the cell A2, H2 using $ to fix $A$2 and $H$2
also change the range to suit your data
Use
Control+Shift+Enter to create an array formula

Then you can use a vlookup or index match to find the other entres

Book2
	A	B	C	D	E	F
1
2	prouct-1-1		prouct-2-1		prouct-3-1
3	prouct-1-2		prouct-2-2		prouct-3-2
4	prouct-1-3		prouct-2-3		prouct-3-3
5	prouct-1-4		prouct-2-4		prouct-3-4
6	prouct-1-5		prouct-2-5		prouct-3-5
7	prouct-1-6		prouct-2-6		prouct-3-6
8	prouct-1-7		prouct-2-7		prouct-3-7
9	prouct-1-8		prouct-2-8		prouct-3-8
10	prouct-1-9				prouct-3-9
11
Sheet1

Cell Formulas
Range	Formula
A2:A11	A2	=IFERROR(INDEX(List!$B$2:$B$100,SMALL(IF(List!$A$2:$A$100=1,ROW(List!$A$2:$A$100)-ROW(List!$A$2)+1),ROWS(A$2:A2))),"")
C2:C11	C2	=IFERROR(INDEX(List!$B$2:$B$100,SMALL(IF(List!$A$2:$A$100=2,ROW(List!$A$2:$A$100)-ROW(List!$A$2)+1),ROWS(A$2:A2))),"")
E2:E11	E2	=IFERROR(INDEX(List!$B$2:$B$100,SMALL(IF(List!$A$2:$A$100=3,ROW(List!$A$2:$A$100)-ROW(List!$A$2)+1),ROWS(A$2:A2))),"")

 

[rshahi]

Thanks a lot Wayne..
that worked like a charm. I've been trying to figure this out for a week now. Should have just asked the professionals...
Thanks again.

 

[etaf]

you are welcome