Comparing data on 2 worksheets

[richzip]

I would like to know if there is a formula or macro that can accomplish the
following, related to pay increases for employees:

Worksheet 1: lists employees ID # (column A), along with their old pay rate
(column B), their new pay rate (column C), and the effective date of the
increase (column D).

Worksheet 2: lists all employees; each employee has 1 row for every date of
the month (some dates might have 2 rows). Data includes employee ID (column
A), date (column B), and a blank column where i would like to insert the pay
rate (column C)

For each employee that is listed in worksheet 1, I would like to copy the
old pay rate. That rate would then be pasted to column C of worksheet 2, for
every date that is less than the effective date listed on worksheet 1. Then,
it would paste the new rate onto every row that has a date that is greater
than or equal to the effective date.

Example:
On worksheet 1, Employee 1 has an old rate of 30, and a new rate of 40, with
an effetive date of 2/15/09
On worksheet 2, the "rate" column should have 30 for every row with a date
of 2/1/09-2/14/09, and 40 for every row with a date of 2/15/09-2/28/09.

 

[richzip]

One other point: If the employee on worksheet 2 is not found in worksheet 1,
then the "rate" column on worksheet 2 should be left blank.