Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel Auto Fill Cells


  • Please log in to reply
1 reply to this topic

#1 Baloo

Baloo

  • Members
  • 243 posts
  • OFFLINE
  •  
  • Location:Ontario, Canada
  • Local time:08:44 AM

Posted 02 June 2010 - 09:50 PM

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.
When life hands you lemons squeeze the lemon juice right in his eye!

BC AdBot (Login to Remove)

 


#2 jase30004

jase30004

  • Members
  • 10 posts
  • OFFLINE
  •  
  • Local time:08:44 AM

Posted 06 June 2010 - 01:36 PM

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

Edited by jase30004, 06 June 2010 - 01:38 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users