Hello all,

New user here, please be gentle

I have been using a master spreadsheet as a pricing tool at work. Until now I've had 8 variables and using a fairly simple (although quite possibly not the best method) IF function to control what I'm doing.

Basic premise is that I have a rate card on one sheet, a labour detail on another sheet and a final pricing summary on a third sheet. Formula I've been using is:

=IF(D5=10,(C5*'Rate Card'!$P$18),IF(D5=9,(C5*'Rate Card'!$P$19),IF(D5=8,(C5*'Rate Card'!$P$20),IF(D5=7,(C5*'Rate Card'!$P$21),IF(D5=6,(C5*'Rate Card'!$P$22),IF(D5="C",(C5*'Rate Card'!$P$23),IF(D5="L",(C5*'Rate Card'!$P$24),IF(D5="O",(C5*'Rate Card'!$P$25),""))))))))

This has been working fine. However I now need to add in a load of other variables which mean I need 3 rate card sheets and to try and pull in numbers based on that. The formula I need is this:

=IF(D6=10,(C6*'Rate Card'!$P$18),IF(D6=9,(C6*'Rate Card'!$P$19),IF(D6=8,(C6*'Rate Card'!$P$20),IF(D6=7,(C6*'Rate Card'!$P$21),IF(D6=6,(C6*'Rate Card'!$P$22),IF(D6="C",(C6*'Rate Card'!$P$23),IF(D6="O9",(C6*'Rate Card - Offshore'!$P$18),IF(D6="O8",(C6*'Rate Card - Offshore'!$P$19),IF(D6="O7",(C6*'Rate Card - Offshore'!$P$20),IF(D6="O6",(C6*'Rate Card - Offshore'!$P$21),IF(D6="O5",(C6*'Rate Card - Offshore'!$P$22),IF(D6="L9",(C6*'Rate Card - Landed'!$P$20),IF(D6="L8",(C6*'Rate Card - Landed'!$P$21),IF(D6="L7",(C6*'Rate Card - Landed'!$P$22),IF(D6="L6",(C6*'Rate Card - Landed'!$P$23),IF(D6="L5",(C6*'Rate Card - Landed'!$P$24),""))))))))))))))))

However this is too many nested functions!

Any ideas on how I get around this?

Basically I have my three rate card sheets which control the rates I can charge for various resources either in the UK or abroad.

In the pricing detail sheet I enter, from a pull down menu, whether they are 10's or O8's or L6's or whatever.

Then on the pricing summary I want the function to look at what type of resource I have selected on my pricing detail, and multiply a cell by the relevant price from the three rate card sheets.

I hope that makes sense and hope someone can point me in the right direction!!

Thanks
Kerry