Commission Calculation Spreadsheet

[LMiller639]

Hello Excel Forum!
This is my very first post here so I'm hoping I do this right. I am creating a spreadsheet that will calculate the compensation due to the agents and employees that work with and for my company. As money comes into my company, I must determine how much of it needs to be paid back out to our sales force. The amount paid may depend on the agent selling the case, the company they sold it with, product type and even the specific product sold.

So far my spreadsheet has been working fairly well under most circumstances. I've established one sheet to hold all of my commission data (i.e. agent name, company name, product type, commission amount, etc.) and another to post the incoming money with sale details. Using combination of concatenate and vlookup, I'm having the incoming money sheet referencing my data sheet to determine if an amount is due and if so, how much and to whom.

My issue is when I need to accommodate multi-tier payouts in special scenarios. For example, agent John Smith has a regional manager Bob Ross. Whenever John Smith sells anything, his manager should ALWAYS be paid. When John Smith sells an Allianz Master X product, he should get a percentage of that sale. When John Smith sells any other Allianz product, he should not receive any compensation at all. When John Smith receives compensation, the amount paid to his manager Bob Ross is reduced by the amount paid to John Smith. I can't figure out how to essentially create an "All Other" scenario so that I can pay John on only Master X products and not pay him on all other products without having to enter data for every single other product individually. Carriers tend to have dozens of products and they like to add/remove them frequently. Not having to take the time to enter everything individually and then maintain it as well is ideal.

I've attached a copy of a portion of my spreadsheet in the hopes that it will help you better understand what I'm up to. If I need to provide any additional information in order for someone to point me in the right direction here, please don't hesitate to ask. Thanks in advance for your help!!!