For Excel Experts only! I need to reference to data sheet 1, and use it to calculate a result on sheet 2.?

Question

1st sheet: On the first sheet, I have a gigantic list of policies and their commission rates. For example: Policy 1: 1st yr commission rate= 8%, 2nd year=4% and 3rd year=3%.

My task is to calculate the commisions earned each fiscal year, based on the commision rate of different policies
In sheet 1:
So if my boss types in the name of the policy, the formula I use should calculate the commission earned automatically, by using the respective commission rates for THAT policy for the first 3 years.

I know the basic excel stuff, but I really dont know how the approach this task! Please Help! Thanks a million!!

Answer 1

You should use the LOOKUP function for that. Go to Functions and click on Get help for this function. It explains how it works and there's an example.

Answer 2

Sheet 2: cells A1= "1st", B1 = .08
A2= "2nd", B2 = .04
A3= "3rd", B3 = .03

Sheet 1: cell A1 = "Amount of money commission is based on"
cell B1 = (amount that you boss types in in currency format)

cell A4 = "Policy"
cell B4 = "Commission Rate"
cell C4 = "Commission"

cell A5 = 1
cell A6 = 2
cell A7 = 3
cell B5 = sheet2!B1
cell B6 = sheet2!B2
cell B7 = sheet2!B3
cell C5 = $B$1*B5
cell C6 = $B$1*B5
cell C7 = $B$1*B5

I did not use "name of policy" in this case but it does the same thing that you are asking for based on the number of the policy. This is to get you started. For the next three years, take the calculations that I have given you and expand it for three years.

Answer 3

If I understand what you are trying to do, you probably want to use either a HLOOKUP or VLOOKUP formula. Unfortunately, I cannot tell you more than that. Excel will step you through the steps, though, if you click on Insert, Function, and then type HLOOKUP. Good luck.

Answer 4

Use an absolute reference to go between pages. Its like F4 I believe or you can just type in dollar signs around the number (ex: $A$1). Hope this helps somewhat.

Answer 5

Sounds like a simple vlookup formula situation. Since your boss expects you handle advanced excel, suggest you look vlookup up.