Here's a play using non-array formulas ..
Sample construct available at:
http://www.savefile.com/files/5315921
48 names - Fortnightly Saturdays rotation schedule
In a sheet: Names, list the 48 names in A1:A48
Put in A49: =A1
Copy A49 down to A96 to repeat the namelist
In another sheet: X
Fill the numbers 1 - 48 across in A1:AV1
(these numbers will be the 48 roster #s)
Put in A2: =OFFSET(INDIRECT("'Names'!A"&ROW(A1)),COLUMN(A1)-1,)
Copy A2 across 48 cols to AV2, fill down to AV49 to populate the schedule
A2:AV49 will return the required 48 rotational rosters / schedules
In a new sheet: Y,
Put in A1: 10-Jun-2006
Put in A2: 24-Jun-2006 (the next fortnight Sat)
Select A1:A2, fill down to say, A96
to fill 2 cycle's worth of fortnight Saturdays till 30-Jan-2010
Put in B1: =MOD(ROW(A1)-1,48)+1
Copy B1 down to B96
This quickly numbers 2 cycles of 1-48, viz.:
1-48 in B1:B48, then 1-48 again in B2:B96
Then, in the sheet with your 48 horizontal "shifts" listed in B1:N48
(assume this sheet is named as: Z)
Insert a new row1, then put in the new A1:
=IF(ISNUMBER(MATCH(TODAY(),Y!A:A,0)),OFFSET(X!A:A, ,MATCH(VLOOKUP(TODAY(),Y!A:B,2,0),X!$1:$1,0)-1),"")
Copy A1 down to A49
A2:A49 returns the required roster from X
depending on the current date
(i.e. the Saturday's date which matches the current date)
[A1 returns the roster #]
And if the current date doesn't match with the Sat dates listed in Y's col A,
then col A will appear "blank".
Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jinvictor" wrote:
what i want is:
a1-a48 different names
b1-n1 different shifts, etc: morning shift, afternoon shift,
night shift, rdo
b2-n2 different shifts again,different as b1-n1
all the way to line 48
every saturday fornight week, we work drop 1 line, say if my name in a1
this fornight, saturday 2 weeks later, my name will be on line 2(b2-n2).
and person in a48 will be in a1.
how can i do it so every 2nd saturday when i open the workbook, it
automaticaly change to new line which i suppose to be.
thanx
--
jinvictor
------------------------------------------------------------------------
jinvictor's Profile: http://www.excelforum.com/member.php...o&userid=34099
View this thread: http://www.excelforum.com/showthread...hreadid=548256