Excel 2007 : Want to make a Order List

[terrywai]

I have already upload the attachment here.

I want to make a order list but don't how to set the formula in excel, hope anyone can help me, it's important for me, please

1.) If the value in the Quantity (column D) is >=1, then will copy all the row to the Order (A13) if <1, skip it

2.) Will have the total weight after the order list was made, e.g. we have 47 kg in my list, then check back the cost from sheet "weight", that is $15 and paste it in somewhere in the order list

Hope these 2 question no need to use the VB program.

Thx everyone

[NBVC]

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.

[terrywai]

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

[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).

[terrywai]

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).

How about if i want to add one more list in this??

=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)),""))
IF(ROW()-COUNTIF(Product2!$D$2:$D$8,">=1")-1<=COUNTIF(Product3!$D$2:$D$8,">=1"),INDEX(Product3!A$2:A$8,SMALL(IF(Product3!$D$2:$D$8>=1,ROW(Product3!$D$2:$D$8)-ROW(Product3!$D$2)+1),ROW()-COUNTIF(Product2!$D$2:$D$8,">=1")-1)),""))

After i add "Product3", and add the above code but i can't accept, what's wrong of my code

Thx

[NBVC]

If you start adding more "products" you will need to begin thinking about using VBA instead... the formula will become bigger and less efficient, otherwise.

[terrywai]

If you start adding more "products" you will need to begin thinking about using VBA instead... the formula will become bigger and less efficient, otherwise.

Sorry, because i still learning for the VBA and my program knowlege is equal to 0....... do you have any reference about that?

[NBVC]

Try posting the question in the Programming forum with all details (do not add complications as you go along as you did with this thread)