Automatically Inserting Data into Cells based data entered in other cells

Hello Guys and Girls,

I am creating an Overtime sheet at work to show who is doing overtime on a set week.

I am currently using a drop down list to enter the names of workers that are due to be doing overtime however what I need to know is how to link data to that workers name. So (looking at supplied excel sheet) when I insert "Worker A" from the drop down list, I want it to automatically fill in Columns F and G (their hourly rate and their overtime rate). Is there a way to do this?

I would also like to know if its possible to link my date columns (again looking at supplied spreadsheet) to the week ending date. I want to use a drop down menu again for the date columns. So if I put in the week ending cell 08/08/2010 my date columns drop down menu will show only the day and dates for that week, eg Mon 2nd, Tues 3rd, Weds 4th...etc.

Basically this is so most of the sheet is drop down menu's and automated. As I have simple formulae already in place to calculate the total overtime cost.

Thank you in advance for any help as to whether this is possible. If anyone knows of a better way of doing what I am suggesting I would of course be open to suggestions.

For first question.

In your Data Sheet enter the actual hourly rates next to each employee.

then the formula in F5, for example, would be:

=IF(B5="","",VLOOKUP(B5,'Data Sheet'!$A$1:$B$7,2,0))

copied down.

In G5,

=IF(B5="","",VLOOKUP(B5,'Data Sheet'!$A$1:$B$7,2,0)*1.5)

copied down.

For second question.

In Data Sheet create a new named range for say cells G1:G6 called "Week" (without the quotes).

Then in G1 enter: =August!$J$2-6, in and in G2 enter: =G1+1 and copy down to G6.

Now your data validation in C5 of August sheet would be List with =Week as the source.