Perhaps one way .. experiment with this set-up ..
In a sheet: Names
-----------------------
Create a lookup table:
Put in A1: Name1, copy down to A10
Enter the names of the 10 people in B1:B10,
viz in A1:A10 ..
Name1 John
Name2 Mary
Name3 Gina
Name4 Robert
Name5 Peter
Name6 Sergio
Name7 Jimmy
Name8 Debra
Name9 Dill
Name10 Dave
In a sheet: Roster
-----------------------
Put in B1:
=VLOOKUP("Name"&MOD(COLUMN(A1)-1,10)+1,Names!$A$1:$B$10,2,0)
Copy B1 across to U1
(This'll fill in the 10 names from sheet: Names,
with the names repeated once into B1:U1)
Put in A2: =ROW(A1)
Put in B2:
=OFFSET(INDIRECT("$"&CHAR(ROW(A$64)+COLUMN(B$1))&"$1"),,MOD(ROW(A1)-1,10))
Copy B2 across to K2
Select A2:K2, fill down to K54
The above will create a roster lookup for the week#s 1 - 53
(The roster will "cycle" the 10 names in B2:K54 as you copy down)
Finally ..
In a sheet: CheckRoster
-------------------------------
Put "Date", "Week#" as labels in A1:B1
Cell A2 is where the date will be entered
Put in B2: =IF(A2="","",weeknum(A2,1))
[ Or, in B2: =IF(A2="","",weeknum(A2,2)) ]
To get the roster in a horizontal manner, in say C2:L2
---------------------------------------------------------------------------
Put in C2:
=IF($A2="","",VLOOKUP($B2,Roster!$A$2:$K$54,COLUMN(B1),0))
Copy C2 across to L2
To get the roster in a vertical manner, in say B2:B12
-------------------------------------------------------------------------
Put in B3:
=IF(A$2="","",VLOOKUP(B$2,Roster!$A$2:$K$54,ROW(A2),0))
Copy B3 down to B12
Note: weeknum() requires the Analysis Toolpak
to be installed and enabled (via Tools > Add-ins)
