# VBA code to calculate amount based on multiple criteria

#### Lisa116

##### New Member
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

#### Attachments

• 1620064273180.png
40.6 KB · Views: 6

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Lisa116

##### New Member
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?

Replies
2
Views
130
Replies
3
Views
74
Replies
3
Views
195
Replies
9
Views
135
Replies
2
Views
156

1,132,640
Messages
5,654,541
Members
418,139
Latest member
nimesh72

### 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.

### Which adblocker are you using?

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

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