How to return cell values from one sheet to another if cell is not blank and go to next one and again

[missrutele]

I have 3 sheets with listed different models there you can indicated how many you need of which model (~300 models). I need to use a formula in another sheet that check these sheets and if cell is not blank (quantity indicated), return information to main sheet (order sheet) - name, barcode and quantity.

For example with smaller amount of models:

in model sheet I have such information and I choose item and indicated needed quantity in column C

[TABLE="width: 232"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Barcode[/TD]
[TD]Name[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]022677030104 [/TD]
[TD]item1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]022677280431[/TD]
[TD]Item2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]022677271934[/TD]
[TD]Item3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]024777670679[/TD]
[TD]Item4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Than in main sheet i need that information would be like this (would shown just ordered goods)

[TABLE="width: 260"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]item[/TD]
[TD]Barcode[/TD]
[TD] Order[/TD]
[/TR]
[TR]
[TD]Item2[/TD]
[TD]022677280431[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Item3[/TD]
[TD]022677271934[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


Please someone help

 

[Scott T]

Excel 2010
	A	B	C
1	Barcode	Name	Quantity
2	22677030104	item1
3	22677280431	Item2	2
4	22677271934	Item3	1
5	24777670679	Item4
model

Excel 2010
	A	B	C
1	Barcode	Name	Quantity
2	22677280431	Item2	2
3	22677271934	Item3	1
4
5
6
7
8
9
10
main

Cell Formulas
Range	Formula
A2	{=IF(ROWS(A$2:A2)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A2))))}
A3	{=IF(ROWS(A$2:A3)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A3))))}
A4	{=IF(ROWS(A$2:A4)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A4))))}
A5	{=IF(ROWS(A$2:A5)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A5))))}
A6	{=IF(ROWS(A$2:A6)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A6))))}
A7	{=IF(ROWS(A$2:A7)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A7))))}
A8	{=IF(ROWS(A$2:A8)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A8))))}
A9	{=IF(ROWS(A$2:A9)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A9))))}
A10	{=IF(ROWS(A$2:A10)>COUNTA(model!$C$2:$C$5),"",INDEX(model!A$2:A$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(A$2:A10))))}
B2	{=IF(ROWS(B$2:B2)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B2))))}
B3	{=IF(ROWS(B$2:B3)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B3))))}
B4	{=IF(ROWS(B$2:B4)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B4))))}
B5	{=IF(ROWS(B$2:B5)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B5))))}
B6	{=IF(ROWS(B$2:B6)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B6))))}
B7	{=IF(ROWS(B$2:B7)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B7))))}
B8	{=IF(ROWS(B$2:B8)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B8))))}
B9	{=IF(ROWS(B$2:B9)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B9))))}
B10	{=IF(ROWS(B$2:B10)>COUNTA(model!$C$2:$C$5),"",INDEX(model!B$2:B$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(B$2:B10))))}
C2	{=IF(ROWS(C$2:C2)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C2))))}
C3	{=IF(ROWS(C$2:C3)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C3))))}
C4	{=IF(ROWS(C$2:C4)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C4))))}
C5	{=IF(ROWS(C$2:C5)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C5))))}
C6	{=IF(ROWS(C$2:C6)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C6))))}
C7	{=IF(ROWS(C$2:C7)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C7))))}
C8	{=IF(ROWS(C$2:C8)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C8))))}
C9	{=IF(ROWS(C$2:C9)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C9))))}
C10	{=IF(ROWS(C$2:C10)>COUNTA(model!$C$2:$C$5),"",INDEX(model!C$2:C$5,SMALL(IF(model!$C$2:$C$5<>"",ROW(model!$A$2:$A$5)-ROW(model!$A$2)+1),ROWS(C$2:C10))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[missrutele]

Thank you, that works.

One problem if I have to collect these data from few different sheets how I should merge it in one formula, with + or OR??

Thank you for your help

 

[missrutele]

Thank you, that works.

One problem if I have to collect these data from few different sheets how I should merge it in one formula, with + or OR??

Thank you for your help

 

[Scott T]

For that I would probable use VBA code or maybe power query.