Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Thanked 0 Times in 0 Posts

    Excel version of IF - WHERE (2004/11.3.3 (061213))

    Still having "fun" learning the complexities of Excel. The current task I'm stuck with is introducing a form of "where" statement into a formula.

    I have a 3 column data set (customer, code, quantity) which I import from our accounting system.
    I want excel to automatically fill out the back order position on various customer tabs based on this raw data.
    The way I picture it in my head is that I want it to do a VLOOKUP on the product codes and quantity where the first column is equal to the name of the customer.

    Has anyone done anything like this?

    Attached is an example doc

    The tabs "Customer One", "Customer Two", "Customer Three", and "Customer Four" have a template showing product codes and descriptions. The tab "Back Orders" shows how much of each product code the individual customers have on back order.

    I need the data from "Back Orders" to populate the various customer tabs.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Excel version of IF - WHERE (2004/11.3.3 (061213))

    Welcome to Woody's Lounge!

    Enter the following formula in cell C3 on the Customer One sheet:

    =IF(A3="","",SUMPRODUCT(('Back Orders'!$A$2:$A$20="Customer One")*('Back Orders'!$B$2:$B$20=B3)*'Back Orders'!$C$2:$C$20))

    and fill down as far as needed. If you wish, you can enter the text Customer One in a cell. for example E1, and refer to that:

    =IF(A3="","",SUMPRODUCT(('Back Orders'!$A$2:$A$20=$E$1)*('Back Orders'!$B$2:$B$20=B3)*'Back Orders'!$C$2:$C$20))

Posting Permissions

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