I have been thinking about a problem for days but still haven't
decided the best way to set up the spreadsheet, last year it was a
logistical nightmare.

I have seventeen trainers who will be able to work one or two sessions
out of nine sessions i.e. Mon-Fri pm, Sat and Sun am/pm and can have a
maximum of five trainees each. Each trainer will only run one training
session per availability i.e. if they are available Mon and Fri they
will only run a course on either Monday or Friday.

Then I have 99 trainees who will be available to attend one or more of
the sessions and I have to allocate as many of them as possible, i.e.

Trainer A can provide training on either Monday pm or Sunday am
Trainer B can provide training on Thursday pm
Trainer C can provide training on Sun pm
etc

Trainee 1 can attend on either Mon or Thur pm
Trainee 2 can only attend on Sunday pm
Trainee 3 can attend on Sunday pm or Monday pm
etc

Can anyone suggest a way to organise this spreadsheet (and, but this
is secondary, then pull the information to a database so that I can
run a letter to the trainer and trainees advising them of their
trainer/trainees and days and dates of their course.)

I know that I haven't made myself very clear so thanks for any help in
advance.

Hi

One way that might get you started would be to setup the following,
In B2 enter Mon AM, C2 Tue AM etc. across to Sun PM in J2
In A3 enter TrainerA, then in A8 enter Trainer B going down column A in
increments of 5 until you have all 17 trainers entered.

Repeat headings B2:J2 in M2:U2
In M3 enter name of first student who can attend that session , then the
next and so on, allocating all students to each column of sessions they
can attend.

In cell B3 enter
=INDEX($M$2:$U$101,COUNTIF(B$2:B2,"<>")+1,COLUMN(A1))
copy across through C3:J3, then copy B3:J3 down through B4:B87
This will allocate all of the students to all of the columns.

In M1 enter
=COUNTA(M3:M101) and copy across through N1:U1
This will give a count of the number of students who can attend each
session.

In B1 enter
=COUNTA(B3:B101)&" / "&M1 and copy across through C1:J1
This will show how many students out of the total number have been
allocated to that session

Now, looking at Trainer A, blank out the formulae in the 5 rows of all
of the columns that they cannot attend.

It's a bit hit and miss, but it should get you started

--
Regards

Roger Govier


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have been thinking about a problem for days but still haven't
> decided the best way to set up the spreadsheet, last year it was a
> logistical nightmare.
>
> I have seventeen trainers who will be able to work one or two sessions
> out of nine sessions i.e. Mon-Fri pm, Sat and Sun am/pm and can have a
> maximum of five trainees each. Each trainer will only run one training
> session per availability i.e. if they are available Mon and Fri they
> will only run a course on either Monday or Friday.
>
> Then I have 99 trainees who will be available to attend one or more of
> the sessions and I have to allocate as many of them as possible, i.e.
>
> Trainer A can provide training on either Monday pm or Sunday am
> Trainer B can provide training on Thursday pm
> Trainer C can provide training on Sun pm
> etc
>
> Trainee 1 can attend on either Mon or Thur pm
> Trainee 2 can only attend on Sunday pm
> Trainee 3 can attend on Sunday pm or Monday pm
> etc
>
> Can anyone suggest a way to organise this spreadsheet (and, but this
> is secondary, then pull the information to a database so that I can
> run a letter to the trainer and trainees advising them of their
> trainer/trainees and days and dates of their course.)
>
> I know that I haven't made myself very clear so thanks for any help in
> advance.

Thanks Roger, I will give it a go.

Hi

One way that might get you started would be to setup the following,
In B2 enter Mon AM, C2 Tue AM etc. across to Sun PM in J2
In A3 enter TrainerA, then in A8 enter Trainer B going down column A
in
increments of 5 until you have all 17 trainers entered.

Repeat headings B2:J2 in M2:U2
In M3 enter name of first student who can attend that session , then
the
next and so on, allocating all students to each column of sessions
they
can attend.

In cell B3 enter
=INDEX($M$2:$U$101,COUNTIF(B$2:B2,"<>")+1,COLUMN(A1))
copy across through C3:J3, then copy B3:J3 down through B4:B87
This will allocate all of the students to all of the columns.

In M1 enter
=COUNTA(M3:M101) and copy across through N1:U1
This will give a count of the number of students who can attend each
session.

In B1 enter
=COUNTA(B3:B101)&" / "&M1 and copy across through C1:J1
This will show how many students out of the total number have been
allocated to that session

Now, looking at Trainer A, blank out the formulae in the 5 rows of all
of the columns that they cannot attend.

It's a bit hit and miss, but it should get you started

--
Regards

Roger Govier

Thank you for your help with my excel formula, can I ask one further
question please.

The spreadsheet works fine but it doesn't allocate the names of the
trainees to any rows above the corresponding entry line, i.e. if the
trainee is in N8 even if I have spaces in say C5 it does enter it but
the correct count is displayed in C1.

I have looked at the formula but cannot see what instruction to change
to correct this.

Is it possible to alter this?

Thanks again.

Hi

Apologies for the delay in response. Some fool chopped through our cable
and I lost all telephone and internet contact for over 12 hours.

I can't quite see what you are saying.
If you have not removed the formula from C5, then it should pick up the
extra trainee.
If you want to mail a copy of your workbook direct to me, I will take a
look and see if I can see what is going wrong.
To mail direct, remove NOSAPM from my address.

--
Regards

Roger Govier


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi
>
> One way that might get you started would be to setup the following,
> In B2 enter Mon AM, C2 Tue AM etc. across to Sun PM in J2
> In A3 enter TrainerA, then in A8 enter Trainer B going down column A
> in
> increments of 5 until you have all 17 trainers entered.
>
> Repeat headings B2:J2 in M2:U2
> In M3 enter name of first student who can attend that session , then
> the
> next and so on, allocating all students to each column of sessions
> they
> can attend.
>
> In cell B3 enter
> =INDEX($M$2:$U$101,COUNTIF(B$2:B2,"<>")+1,COLUMN(A1))
> copy across through C3:J3, then copy B3:J3 down through B4:B87
> This will allocate all of the students to all of the columns.
>
> In M1 enter
> =COUNTA(M3:M101) and copy across through N1:U1
> This will give a count of the number of students who can attend each
> session.
>
> In B1 enter
> =COUNTA(B3:B101)&" / "&M1 and copy across through C1:J1
> This will show how many students out of the total number have been
> allocated to that session
>
> Now, looking at Trainer A, blank out the formulae in the 5 rows of all
> of the columns that they cannot attend.
>
> It's a bit hit and miss, but it should get you started
>
> --
> Regards
>
> Roger Govier
>
> Thank you for your help with my excel formula, can I ask one further
> question please.
>
> The spreadsheet works fine but it doesn't allocate the names of the
> trainees to any rows above the corresponding entry line, i.e. if the
> trainee is in N8 even if I have spaces in say C5 it does enter it but
> the correct count is displayed in C1.
>
> I have looked at the formula but cannot see what instruction to change
> to correct this.
>
> Is it possible to alter this?
>
> Thanks again.

On Tue, 13 Mar 2007 13:38:47 -0000, "Roger Govier"
<(E-Mail Removed)> wrote:

>Hi
>
>Apologies for the delay in response. Some fool chopped through our cable
>and I lost all telephone and internet contact for over 12 hours.
>
>I can't quite see what you are saying.
>If you have not removed the formula from C5, then it should pick up the
>extra trainee.
>If you want to mail a copy of your workbook direct to me, I will take a
>look and see if I can see what is going wrong.
>To mail direct, remove NOSAPM from my address.

Thank you Roger, I have sent you a copy.

Wendy

Hi Wendy

Copy received modified and returned.
The problem was leaving gaps between the list of trainees in each of the
9 columns.
Each column should contain a contiguous list of names, no gaps.
If you want to give trainees preference in order of receiving training,
then list them in the columns in a priority order, or on a first come
first served basis
--
Regards

Roger Govier


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Tue, 13 Mar 2007 13:38:47 -0000, "Roger Govier"
> <(E-Mail Removed)> wrote:
>
>>Hi
>>
>>Apologies for the delay in response. Some fool chopped through our
>>cable
>>and I lost all telephone and internet contact for over 12 hours.
>>
>>I can't quite see what you are saying.
>>If you have not removed the formula from C5, then it should pick up
>>the
>>extra trainee.
>>If you want to mail a copy of your workbook direct to me, I will take
>>a
>>look and see if I can see what is going wrong.
>>To mail direct, remove NOSAPM from my address.
>
> Thank you Roger, I have sent you a copy.
>
> Wendy