Excel 2007 : Want to make a Order List

Put this formula in A13

=IF(ROWS($A$1:$A1)>COUNTIF($D$2:$D$7,">=1"),"",INDEX(A$2:A$7,SMALL(IF($D$2:$D$7>=1,ROW($D$2:$D$7)-ROW($D$2)+1),ROWS($A$1:$A1))))

Then hold the CTRL and SHIFT keys down and hit ENTER. You will notice { } brackets appear around the formula. Then copy the formula down and across to column D.

Note: If you don't want VBA involved, you will need to allow for some blank rows underneath so that the cells will either fill or be "blank" based on your quantities....

Note2: If you make adjustments to this formula, you will have to reconfirm it with CTRL+SHIFT+ENTER each time, and then re-copy it over to the other cells.

Sorry, NBVC, can i help me one more
i modify the list now
if i use your formula into the list, how and where i paste it.

Now, i have 2 product list, and also the same condition, if the quantity column is >=1, paste the whole row to the "Order" Sheet

Thanks NBVC

See attached...

Formula in A2:

=IF(ROW()-1<=COUNTIF(Product1!$D$2:$D$8,">=1"),INDEX(Product1!A$2:A$8,SMALL(IF(Product1!$D$2:$D$8>=1,ROW(Product1!$D$2:$D$8)-ROW(Product1!$D$2)+1),ROW()-1)),IF(ROW()-COUNTIF(Product1!$D$2:$D$8,">=1")-1<=COUNTIF(Product2!$D$2:$D$8,">=1"),INDEX(Product2!A$2:A$8,SMALL(IF(Product2!$D$2:$D$8>=1,ROW(Product2!$D$2:$D$8)-ROW(Product2!$D$2)+1),ROW()-COUNTIF(Product1!$D$2:$D$8,">=1")-1)),""))

confirmed with CTRL+SHIFT+ENTER and copied across 4 columns and down to row 17 (to cover all 8 items from each product sheet).