Forumla to find the correct houlry rate for staff

[tonywatsonhelp]

Hi Everyone,
I really hope you can help

I have two sheet
Caphours
Employees

in Caphours column "i" is Week and J is Employees name. L is where i want the formula to find the employees hourly rate.

in Employees
Column C is Start date
D End Date,
G Employees Name
and L Hourly rate

so heres what i want,
Caphours holds rows of data about each employee, I want to find the correct houly rate for the week in "i" for that employee

So If i was doing this in english it would go something lkike this

for each row, take the employees name in column J and the week in i
find the first row this employees name apears on in sheet "Employees" Column G, Look at the Start and end dates and if the weeks date is between thise dates then return hourly rate as L, if not find the next time his name apears and do the same check, if none are correct take the last time his name apears as the correct answer.

 

[sijpie]

You won't be able to do this with a formula, I think. But a macro that runs automatically could do this no problem. Let me know if you want to go that route