VBA code to calculate amount based on multiple criteria

[Lisa116]

I am thinking that maybe VBA code is what I need to get the formula on the statistic sheet to match the company to the company’s data on the Inputs sheet that the individual is with and use that data. The problem I am having is getting the formula to go from one company’s data to the next on the Inputs sheet as well as on the Calc sheet. On the Statistic sheet, the formula is copied down the page, calculating each individual’s life benefit amount based on the individual’s information and the company’s data on the Inputs sheet. On the Inputs sheet, each company’s information is in columns and on the Calc sheet each company’s information is in two columns going down the page.

Formula in F2 on the Statistic sheet:

=IF(XLOOKUP(C2,Inputs!$B$1:$E$1,Inputs!$B$4:$E$4)="Yes",IF(E2="E",IF(Inputs!B3="Flat",Inputs!$B$8,IF(D2*Inputs!$B$9>Inputs!$B$8,Inputs!$B$8,D2*Inputs!$B$9)),IF(E2<>"E",IF(Inputs!$B$3="Flat",Inputs!$B$12,IF(D2*Inputs!$B$13>Inputs!$B$12,Inputs!$B$12,D2*Inputs!$B$13))))*INDEX(Calc!$B$3:$B$7,MATCH(IF(AND(Calc!$A$3:$A$7>B2),MIN(Calc!$A$3:$A$7),MAX(IF(Calc!$A$3:$A$7<=B2,Calc!$A$3:$A$7))),Calc!$A$3:$A$7,0)),IF(E2="E",IF(Inputs!$B$3="Flat",Inputs!$B$8,IF(D2*Inputs!$B$9>Inputs!$B$8,Inputs!$B$8,D2*Inputs!$B$9)),IF(E2<>"E",IF(Inputs!$B$3="Flat",Inputs!$B$12,IF(D2*Inputs!$B$13>Inputs!$B$12,Inputs!$B$12,D2*Inputs!$B$13)))))

My issue is when the company changes, for instance on C3 below, the company is now on C1 of the Input sheet and in my formula all the “Inputs!B” need to read “Inputs!C” and all the “Calc!B3:B7” need to read “Calc!B10:B14”.

thank you in advance for any help.
Lisa

 

[Lisa116]

Question: Can I enter the formula above into a module and tweek the formula to refer to each entity's information, then on the sheet"statistic" in cell F2 have a formula that matches the entity to the corresponding module which will then run the formula?