using dates and lookup functions

[john stevens]

I am doing a labour tracker for work. it is laid out in a
fashion that lets me see how many staff i have in at any
given time of the day. My problem is I have people who
work on a 1 week 2 week and 4 week rotas. I want one work
book with 7 sheets in it 1 for each day of the week and
the people on 1 week fixed contracts keyed in and locked
(easy enough). I then want a seperate workbook to key all
the 2 week (replicated over the four weeks) and four week
contracts into it.

I have entered the today function into cell A41 on each
sheet in the main labour tracker.

i want excel to read from this cell each time the
workbook is opened

decide which week in the 4 week cycle we are on according
to that date 15/07/2003 = week 1; 23/07/2003 = week 3 etc.

and pull in the correct shifts of each person from the
other workbook for that week.

I know whatever formula is used will have to go into
every cell
for example pam would require the formula in all cells
b4 - ae4 sunday to satuday. i just have no idea where to
start with the formula.

if any of you can help me work this out I would be most
grateful

I can send you a copy of what i have so far if it makes
it easier to understand what i am doing.