Function for price qoutation help

[winnie123]

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

 

[winnie123]

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,MATCH(B4,PriceLists!$A$1:$A$89,0),MATCH($B$1,PriceLists!$A$1:$N$1,0)))*$C$2

I then just add up all the prices for the total price.