# Need Function that will find ordered product, and display the product code in a 2nd workbk

1. ## Need Function that will find ordered product, and display the product code in a 2nd workbk

Here is what I am trying to do. The customer receives the file titled "OrderForm.xls", and enters the quantity of each product that they wish to purchase. I have entered quantities for 4 products as an example.

I am trying to enter a formula/function into the second file (called "Bill_Of_Ladding.xls") beginning at cell "A19" that will look at Column "C" in the OrderForm.xls file, beginning at cell "C13", and continuing through cell "C40". The function would print the product code in Column "A" of the "Bill_Of_Ladding.xls" file for the first product that is ordered. In this case, that would be product code 215 (1% LowFat White Milk). Likewise the function continues to look at the "non zero" numbers in Column "C" of the order form, and it would print the corresponding product code in cells A20, A21, etc., until it has printed all products that have an ordered quantity.

I don't know how to do this. Your help is greatly appreciated.

2. ## Re: Need Function that will find ordered product, and display the product code in a 2nd wo

Hi rollerden,

Try using below array formula:-

{=OFFSET(OrderForm.xls!\$A\$1,SMALL(IF(OrderForm.xls!C\$12:C\$40<>"",ROW(OrderForm.xls!\$A\$12:\$A\$40)-1,""),ROW(\$A1)),0)}

See attached:- rollerden.zip

Regards,
DILIPandey

<click on below 'star' if this helps>

3. ## Re: Need Function that will find ordered product, and display the product code in a 2nd wo

Perfect, that is exactly what I needed. THANK YOU!-6

4. ## Re: Need Function that will find ordered product, and display the product code in a 2nd wo

You are welcome rollerden.... cheers

Suggest you to mark this thread as [SOLVED].. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1