Automate a procedure

[shonaart]

Hi
Please can you give me some advice.

1. I have two spreadsheets in one workbook.
2. First spreasdheet has employee ID number in ( column A ), ( Column B ) = dates From - To when the person was on leave. ie 15 Nov 09 - 25 Nov 09 ( Maybe I have to change this part to make easier for excel to recognise )
3. Second spreadsheet : Column A : ID number of the Employee and from Column B to Column E has other info of the employee.
4. Column F1 to Column AI1 has each day of the month at the top.( Heading )

How can I get an X placed in the day of the month which the person was off. ( leave)
Basically look the leave spreadsheet then go find the staff ID ( Column A ) and then look at the Heading and find the day of the month ( F1 to AI1 ) and then add and X or anthing there.

Please can you tell me how I can make easier. I have to do 1 about 500 staff who go on leave and sick etc.

Thanks

 

[Andrew Poulsom]

If you have start date in column B and end date in column C you can use a formula like:

=IF(AND(VLOOKUP($A2,Sheet1!$A:$B,2,FALSE)<=F$1,VLOOKUP($A2,Sheet1!$A:$C,3,FALSE)>=F$1),"X","")

in F2 copied down and across.