Help required with EXCEL pricelist coding

[petrosky]

Hi,

I hope this is the appropriate forum for this question and apologise if it's not...

OK, I have worksheets for both 'SLR bodies' and 'SLR Lenses' which are text imports that contain these 4 columns.

Prod code, Prod Desc, List Cost, Average Cost

I then have another worksheet which is the proposed pricelist our sales guys will use. As it stands I use VLOOKUP for the Current Costings of both the body and the lens and add mark-up.

Each cell representing the body and lens SELL PRICE is "coded" as follows...

=IF(D32=0,SUM(IF(VLOOKUP(A8,SLRBodies,4,FALSE)=0,VLOOKUP(A8,SLRBodies,3,FALSE),IF(VLOOKUP(A8,SLRBodies,4,FALSE)>0,VLOOKUP(A8,SLRBodies,4,FALSE)))+IF(VLOOKUP(D4,Lenses,4,FALSE)=0,VLOOKUP(D4,Lenses,3,FALSE),IF(VLOOKUP(D4,Lenses,4,FALSE)>0,VLOOKUP(D4,Lenses,4,FALSE))))*D33,D32/1.1)

Basically this allows the price to be overridden by me (D32 in this case) and the mark-up to be manually adjusted per product(D33)

I wonder if this UGLY expression could be written as a function and, if so, how?

Even if the expression could be simplified, I would appreciate the help.

I am happy to email the workbook in it's entirity should anyone require it.

Regards,

Peter.

Remember- It's nice to be important,
but it's important to be nice
Remember- It's nice to be important,
but it's important to be nice