Complex problem involving payout calculation with multiple inputs from different sheets

Hey guys,
I have a major project that is giving me a run for my money. I need to build a report that will allow us to copy a report from another excel workbook into this one, then have it automatically put out the expected payout based on the breakout of codes used in a visit type and the allowed fees in the fee schedule.

I created an example which has been blinded for legal reasons.

https://docs.google.com/spreadsheets...it?usp=sharing

I'm trying to figure it out, but given the complexities of the formulas and such, I thought I'd reach out here.

**NOTE: there are over 250 different Insurance types and over 35 different visit types. The most complex visit type has 27 different codes included. **