+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    05-16-2012
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    4

    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. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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