Writing a Formula based on UserForm input

**MichelleW** · 11-06-2009, 10:25 AM

I am trying to write a formula based on what the user inputs in the UserForm.

The formula is going to be: =Max(all the investor prices based on what the user chooses).

The UserForm has two columns, Conforming and Government, each column has the same investors. First I need to determine whether the loan is conforming or government by looking at the product code 206 cells to the left of the activecell, which is the cell which in the formula needs to be written. If it is conforming, I need the formula to include the investors chosen on the UserForm for conforming loans, check boxes 1-6, if it is a government loan, check boxes 7-10.

Each row is a different loan, investor prices are also in the same row. I believe I will have to set the column offset in which the investor's price exists, it will be the same offset whether it is conforming or government. I need to create a loop function to set the formula for all the loans.

Any help would be appreciated, let me know if you need more info to understand what I am trying to do here. Thanks!

**Whizbang** · 11-06-2009, 10:44 AM

Please post an example workbook. If information is sensitive, then make up a dummy workbook.

Mostly, do you have the userform already, and you just need to transfer data from it back to the worksheet, or do you need assistance with making the form?

**MichelleW** · 11-06-2009, 11:15 AM

I've attached a sample of the data. The formula I'm writing needs to go in column K, Best Price (=max(investor1, investor2, investor3...) whatever investor user chooses for that product.

To determine which product, I need it to look at the code in Column D, Loan Program. I was thinking of using the Left function to look at the first letter in the code as the term can vary. C= conforming, F= government

I do already have a userform created.

Left column, Conforming:
CheckBox1: Investor 1
CheckBox2: Investor 2
CheckBox3: Investor 3
CheckBox4: Investor 4
CheckBox5: Investor 5
CheckBox6: Investor 6

Right Column, Governent
CheckBox7: Investor 1
CheckBox8: Investor 2
CheckBox9: Investor 3
CheckBox10: Investor 4