Basic Roster

[mattlong]

Hi,

I'm looking to create a simple roster that will allocate a name from a list to a cell within my spreadsheet. Whether this is by VB or formula dosen't worry me.

My spreadsheet looks like the following:

A1 B1 C1 D1

A8 Date am/pm Name
A9 01/01/07 AM Jim
A10 01/01/07 PM Jane
A11 07/01/07 AM Mark
A12 07/01/07 PM Leroy
A13 14/01/07 AM Peter
A14 14/01/07 PM Jim

So basically what I need to do is from a list of names
ie.

Jim
Jane
Mark
Leroy
Peter
Jim

Allocate their name to cell D9:D14 in a rotational format. Take note of the dates, there are two shifts per date. No one person can be rostered on twice in a row (or twice on the same day). I would also like to stipulate how many times each person is rostered on in total.

For example, say....I only want Jim to be rostered on 3 times for the entire roster.

Having said all of this, I will also have to input the cells I want populated, as I cant have the whole spreadsheet filling with names.

I hope this makes sense!?!?

Thanks to anyone who can help!! This would save HOURS of work! :D

Matt

 

[mattlong]

The table didn't quite turn out right...here it is again..

<TABLE border="2">
<TD> A</TD> <TD> B</TD><TD> C</TD> <TD> D</TD>

<TR> <TD> -</TD> <TD>- </TD> <TD> -</TD> <TD>-</TD> </TR>

<TR> <TD> A8</TD> <TD> Date</TD> <TD> AM/PM</TD> <TD>Name
</TD> </TR>

<TR><TD>A9</TD><TD>01/01/07</TD><TD>AM</TD><TD>Jim</TD></TR>

<TR><TD>A10</TD><TD>01/01/07</TD><TD>PM</TD><TD>Jane
</TD></TR>

<TR><TD>A11</TD><TD>07/01/07</TD><TD>AM</TD><TD>Mark
</TD></TR>

<TR><TD>A12</TD><TD>07/01/07</TD><TD>PM</TD><TD>Leroy
</TD></TR>

<TR><TD>A13</TD><TD>14/01/07</TD><TD>AM</TD><TD>Peter
</TD></TR>

<TR><TD>A14</TD><TD>14/01/07</TD><TD>PM</TD><TD>Jim
</TD></TR>

</TABLE>