Creating a Parking Roster in Excel

[teebird]

Hi All
I want to set up a roster in excel to record staff members using allocated car parking bays - so that when I had a new staff member, it will allow the exsisting staff members to move down so each member has the car park for a 2 week period.

I am not really sure how to go about this. Any help would be great.

 

[SkipVought]

Hi,

1. How many allocated spaces

2. How many staff

3. The allocation rules are

a) each staff gets one allocated space for 2 weeks

b) when a new staff arrives, what happens to the TIMING of the allocation?
New staff if FIRST in line to get the NEXT allocated space.
New staff if LAST in line to get the NEXT allocated space.
Other.

c) when staff leaves, what happens to the TIMING of the allocation?
everything shifts to fill the empty spot.
other.

Skip,

_[red][/red]

 

[teebird]

Hi Skip

Many thanks for your response.

1. Three allocated spaces

2. 20 (but there can be up to 60 staff)

3a. Yes - each staff gets one allocated space for a 2 week period.

3b. New staff are LAST in line to get the NEXT allocated
space.

I was thinking somehting like this...

12-16 March
1. Betty - Park 1
2. John - Park 2
3. Sally - Park 3
4. Charlie -
5. Billy -
6. Terry -

19-23 March
4. Charlie - Park 1
5. Billy - Park 2
6. Terry - Park 3
1. Betty -
2. John -
3. Sally -

29-30 March
1. Betty - Park 1
2. John - Park 2
3. Sally - Park 3
4. Charlie -
5. Billy -
6. Terry –
7. Matt – (new person)

I want the ability to add a new person at any time and they will automatically slot in at the end.

Not sure if I am clear enough.

Many thanks

 

[SkipVought]

Employee list as a named range...
[tt]
Employees
Zeb
Ann
Fred
Sue
Mat
[/tt]

Set up a schedule like this...
[tt]
Period Start Spaces Assignment EmpName
3/19/2007 1 1 Zeb
3/19/2007 2 2 Ann
3/19/2007 3 3 Fred
4/2/2007 1 4 Sue
4/2/2007 2 5 Mat
4/2/2007 3 1 Zeb
[/tt]
Assignment formula...
[tt
C3: =IF(C2+1>COUNTA(Employees),1,C2+1)
[/tt]
EmpName formula
[tt]
D2: =INDEX(Employees,C2,1)
[/tt]

Skip,

_[red][/red]

 

[dallen43]

Skip,

That is a great solution. For myself, I would use a simpler method.

Assuming that newer employees have higher employee numbers, I would fill in 3 columns: Empoyee Number, Employee Name, and date (last date parking was assigned).

Weekly:

1) Sort on Date and Employee number.

2) Add any new employees to the end with their hire date.

3) Assign Parking to the 1st three employees and update their dates.

4) Save the sheet.

Next week, repeat the process.

It is a simple process that doesn't require any formulas and assures that new employees are placed correctly in rotation.