VBA code to calculate amount based on multiple criteria

Lisa116

New Member
Joined
Jul 19, 2019
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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”.

1620064232939.png

1620064316172.png

1620064357833.png

thank you in advance for any help.
Lisa
 

Attachments

  • 1620064273180.png
    1620064273180.png
    40.6 KB · Views: 6

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Lisa116

New Member
Joined
Jul 19, 2019
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,136,868
Messages
5,678,224
Members
419,753
Latest member
Vj3006

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top