Creating working schedule in Excel

[Tompanilla]

Hi!
I am trying to computerize a working schedule in excel with parameters that has to be matched automatically and noted in columns and rows.

Our company has 40 employees all divided in to workgroups of 2 in each.
There are 20 workgroups where all those 20 workgroups has their own working schedule depending of weekday which gives that there are 20 workingpasses every day.

I have written a factsheet (sheet1) which looks like this.

Monday-Thursday
A10 B10 E10 H10
Pass Workingtime Hours Note
1 19:30-05:00 9,5 Mon-Tue 17:00-01:00
2 17:00-03:00 10 Mon-Tue 16:00-01:00
aso down to Pass 20

Friday
A40 B40 E40 H40
Pass Workingtime Hours Note
1 20:00-06:00 10 Jour
2 09:00-17:00 8 -
3 Free 0 0
aso down to 20

Saturday
A70 B70 E70 H70
pass and sheme like above

Sunday
A100 B100 E100 H100
pass and sheme like above

Then I have a datesheet (sheet3)
A10 B10 C10 D10 E10 F10 G10 H10
Date Weekday Pass Hours Workingtime Note Pass Hour
2009-04-01 Wednesday 1 10 19:30-05:00 Mon-Tue 17:00-01:00 2 8

As you can see I want Date, Weekday, Pass, Hours, Workingtime and note for all passes
after each other in one row from left pass no 1 to right pass 20.
I want Excel to fill like this for all days in the month.

It has to give the right information in notes corresponding to weekday.
By this it has to think like this: If pass 1 monday-thursday, then hours is .. and workingtime is .., and the corresponding note is ... Aso for all passes 1-20
It also has to check if Friday, saturday or Sunday and fill in the right cells in sheet 3 from sheet 1.

Hope anyone can give an hint how to autofill sheet 3 with corresponding facts from sheet 1 depending on weekday and pass.

Thays for all possible help

 

[Tompanilla]

I found this formula that works for me.

=IF(AND(A13=1;B13="monday");K2;"")&IF(AND(A13=2;B13="monday");K3;"") aso.