Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for price qoutation help
Hi,
I have a big problem and I just dont know which route to take. I have a product list for which i have set up data validation so that the user can select product and acessories. I need to be able to add up the prices for each value selected. The prices will depend on Currency and Customer level. I have set up data validation for these also. The problem is how do I match the customer level/currency for the product type plus the accessories. At the moment I have the following w/s with the Data Entry w/s for the lookup list w/s for the Euro price list w/s for the Sterling price list w/s for the Usd price list Data entry is set out A = Cust Level B = Currency C = Product Type D = Product E - Q = Acessories The format of the price list is set out Col A Row 7 downwards contain the Product Type Header, then the products in that group, then the Acessories for that product, Then the next product header, then the product,then the accesories for that product and so on. Eg Bill Acceptors Lumina Lumina Rotor Ardac Elite - full system - no bezel - ccTalk / ID003 Ardac Elite - full system - no bezel - Netplex Bill Acceptor Accessories Lumina rotor Ardac Elite - bezel Ardac Elite - Head only - ccTalk / ID003 Ardac Elite - Head only - Netplex Ardac Elite - cashbox Ardac Elite - chassis Ardac Elite - Rear access chassis premium The prices start in Column B8 - N94 The customer Level start B4 - N4 Can anyone suggest anything to help me. I was thinking of trying to use Match and lookup but not sure how to get there. Also when the currency is selected I would need to go to that specific sheet or multiply the end result with an exchange rate based on just the sterling w/s. Please help. Thanks Winnie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for price qoutation help
I managed to sort out by using index and match.
Got rid of the Eur and Usd w/s and put a mulitplier at the end of the formula. Took out blank rows on the Pricelist w/s. C2 lookups the exchange rate based on B2. =LOOKUP(B2,Lists!AA1:AB3) =IF(OR(B4=""),"0",INDEX(PriceLists!$A$1:$N$89,MATC H(B4,PriceLists!$A$1:$A$89,0),MATCH($B$1,PriceList s!$A$1:$N$1,0)))*$C$2 I then just add up all the prices for the total price. "winnie123" wrote: Hi, I have a big problem and I just dont know which route to take. I have a product list for which i have set up data validation so that the user can select product and acessories. I need to be able to add up the prices for each value selected. The prices will depend on Currency and Customer level. I have set up data validation for these also. The problem is how do I match the customer level/currency for the product type plus the accessories. At the moment I have the following w/s with the Data Entry w/s for the lookup list w/s for the Euro price list w/s for the Sterling price list w/s for the Usd price list Data entry is set out A = Cust Level B = Currency C = Product Type D = Product E - Q = Acessories The format of the price list is set out Col A Row 7 downwards contain the Product Type Header, then the products in that group, then the Acessories for that product, Then the next product header, then the product,then the accesories for that product and so on. Eg Bill Acceptors Lumina Lumina Rotor Ardac Elite - full system - no bezel - ccTalk / ID003 Ardac Elite - full system - no bezel - Netplex Bill Acceptor Accessories Lumina rotor Ardac Elite - bezel Ardac Elite - Head only - ccTalk / ID003 Ardac Elite - Head only - Netplex Ardac Elite - cashbox Ardac Elite - chassis Ardac Elite - Rear access chassis premium The prices start in Column B8 - N94 The customer Level start B4 - N4 Can anyone suggest anything to help me. I was thinking of trying to use Match and lookup but not sure how to get there. Also when the currency is selected I would need to go to that specific sheet or multiply the end result with an exchange rate based on just the sterling w/s. Please help. Thanks Winnie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PRICE function, Pls help | Excel Worksheet Functions | |||
Reverse PRICE function | Excel Worksheet Functions | |||
Price Function | Excel Worksheet Functions | |||
Price Function Error? | Excel Worksheet Functions | |||
Financial Function : Price | Excel Discussion (Misc queries) |