Excel 2007 : Data Validation with multiple references

I've been trying to tackle this problem for weeks now and am finally asking for help.

I have a large workbook with multiple sheets requiring similar validation. I know I can stamp the same formulas from one page to the next but I'mhaving trouble with the validation system itself.

Its a multi-level validation that is dependent on more than 1 factor.

Item 1 is the product type, selecting the product type changes the drop down to the left to supply the vendor information for that product type.

Item 2 is the specific product within the type chosen which changes based on the previous 2 choices.

Item 3 is the state in which the product is being purchased.

Finally all this data is used to process the specific commission percentage the vendor will recieve for the purchase.

The commision percentage needs to change based on the data input from the previous entries and I can't wrap my head around the formula I need to get it work.

Any help would be appreciated in order to get rid of this migraine

Here is an example file, the fields required are listed below, the string should resolve by calling the correct commission based on the data provided.

Age= Applicants Age

State = State of residence

Policy Type= type of product applied for

Carrier= Vendor the client is applying with and is activated based on previous cell selection

Plan Code= type of plan applied for and activates based on previous cell selection

Med Status= clients health status and is not dependent on previous selections but impacts the final result

Commission % = the cumulative result of all the above stated data entered so as to auto populate the correct commission percentage without the possibility of user error. The field should be dynamic in that some commissions are percentage based and some are flat rate based on the data provided.

Thanks for the help.
Example.xlsx

I've put together an example cleint listing and I've taken it as far as I can. It terminates at S3 & S4 (highlighted in yellow) where the problem lies. Once the formula for this field is resolved then the rest of the data line wil be able to be ompleted. The data sets to the right of the problem field should all have correct formula's but they rely on that single field for the primary dat required for calculaing everything beyond it.


Example.xlsx

S3 needs to show a single result based on the previous selections. It needs to be foolproof so there isn't any possibility for user error.

The data in S3 is mixed based on the cumulative data ie. age 67 is WA would be a certain percentage based on the product and vender selected, but the same product and vender selection may show an absolute dollar ammount in OR or ID.

DOn't worry about the number of posts to resolution this was dropped on me and its turned into a very complicated situation.

Just checking on the sttus here, its been a while. has there been any progress?