Results 1 to 2 of 2
2007-07-30, 04:48 #1
- 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.
2007-07-30, 06:42 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 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))