Add program to Order List

Hope anyone can help me

Now the list is, if the quantity column for the "Productx" is >=1 than whole row will add to the "Order", if not skip it, i have the formula in the "Order" block A2.

i will have about 6-7 list for "product", now in my file is only 2 Product list (Product1, Product2) and now i want to add more for this, like Product3, Product4....... so i think i need to add program for this and also some expert user also tell me need to add VBA for this.


Hope you can help me to solve this question it's very urgent

There's no way that array formula you were building was going to survive expansion to 6 sheets and remain usable. Here's a simpler approach made possible by the addition of a KEY column added to each of the Product Sheets. The KEY column simply flags a row when it is appropriate to include in the ORDER sheet by watching the QTY column.

Now, the KEY column for each Product sheet flows from the KEY column of the previous Product sheet.

Then, to make it visually understandable, I added an "ITEM" column A to the Order Sheet. The formula in B2 now compares the ITEM number to MAXimum "key" value. If it's higher, the row will suppress. If it's NOT higher, then a simple INDEX/MATCH is used on the first Product sheet looking for Item #1.

If it's NOT found and an error occurs, the IFERROR function I installed causes the "search" to continue on the next Product sheet...and so on through all 6 product sheets.

Here's the IFERROR code:

Please Login or Register  to view this content.

1. Open up your workbook.
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet.

The one formula in B2 is designed so it can be copied down and over as far as needed and it will work. Here's the HUGE formula that results, but it's all INDEX/MATCH stuff so should remain robust:

=IF($A2>MAX(Product7!$F:$F),"",iferror(INDEX(Product1!A:A,MATCH($A2,Product1!$F:$F,0)),
iferror(INDEX(Product2!A:A,MATCH($A2,Product2!$F:$F,0)),
iferror(INDEX(Product3!A:A,MATCH($A2,Product3!$F:$F,0)),
iferror(INDEX(Product4!A:A,MATCH($A2,Product4!$F:$F,0)),
iferror(INDEX(Product5!A:A,MATCH($A2,Product5!$F:$F,0)),
INDEX(Product6!A:A,MATCH($A2,Product6!$F:$F,0))))))))


This technique should scale up smoothly.

Warning, I wasn't able to get the formula to expand to a 7th sheet, so 6 product sheets may be the max for this approach, unless someone spots something I did wrong that would allow the IFERROR() stuff to continue...

See attached for another non-vba method...

You will need to download and install a free addin called Morefunc.xll from here:

Morefunc.xll

Then insert new column A in each sheet...

In Product1 sheet enter formula in A2:

=IF(E2>0,MAX($A$1:A1)+1,0) and copy down.

Do this for all Product sheets.

In A1 of Product Sheet2 forward, enter formula:

=MAX(Product1!A:A)

where you reference previous sheet.

then in Order sheet, insert new column A

Enter formula in B2:

Please Login or Register  to view this content.

copy it across and down

See attached...

Note: You will not see results if you don't download all first..

Another way attached without having to download external addins....

First list all you Product sheets in a range somewhere (I listed in M2:M3)

Then name that range through Insert|Name|Define... call it "TabNames" (without the quotes).

Then do same as above, except formula in B2 of Order sheet is now:

Please Login or Register  to view this content.

which must be confirmed with CTRL+SHIFT+ENTER and copied down and across.

Thx NBVC, but i have 2 question for this one

1.)If i want to send this list to our customer, are they need to download the file "Morefunc.xll " ??

2.)Can i add more list?? What is the maxmium number?

There is a feature that comes with the addin that allows you to embed the functions in the workbook so that others do not need to load the addin... it will be under Tools|Morefunc

I think you can add quite a bit of workbooks.. but I am uncertain exactly how many.. probably as large as you have available memory.

Yes, you do. Also adjust the ranges $A$1:$E$20 to suit your actual possible data

I guess you figured out that you need to copy the formula over to the other columns and down