BleepingComputer.com: Excel Auto Fill Cells

I have an excel sheet that tracks my Umpiring schedule and pay rate. I have created drop down lists for populating cells for Umpire position and for division of play. I have the list of the pay rates for each division and each umpire position (Plate, Base and rookie House League, PeeWee etc) What I want to do is automatically populate the Pay Rate column based on the information in the Umpire Position and Division columns. Should this be done using a macro, conditional statement or what? i am not very well versed in Excel so I would appreciate any help offered.

I would use the VLOOKUP function to populate those results. To do this, you would need to create a list of the the position names, with their coresponding rates and a list of the divisions with their rates. I assume these are added together to get the total pay for a position and division. The lists will each need to be sorted Ascending. They can be anywhere in your spreadsheet and hidden or on another worksheet in the workbook.. which can also be hidden.

For Example if the following was typed into A1:B11:
position p_rate
base 1.2
plate 1.1
rookie 1.3
third 1.4

division d_rate
1 2.1
2 2.2
3 2.3
4 2.4
(assume the position and divison names and the following numbers above are in separate columns.. they do not paste well here in the forum.)

Assuming A14 is where you populate the position and B14 is where the division is populated. Then using VLOOKUP, you can write a formula to look up the values. In C14, I would type the following:
=VLOOKUP(A14, $A$2:$B$5,2,)+VLOOKUP(B14,$A$8:$B$11,2,)

In the above example, if you populated "base" in A14 and "1" in B14, the result in C14 would be 3.3.

If you have your lists named as an array, you could interchange the range (i.e. $A$2:$B$5 )with the array name in your formula.

jase

This post has been edited by jase30004: 06 June 2010 - 01:38 PM