Planning training in Excel

[scarlet]

I have been tasked to organise training for my company and they have
asked me to use MS Project, which has totally blown my mind. Having
done some investigating Project will not give me the answers that I
need surprisingly, but I am hoping Excel will

I am really trying hard to figure a way of schedulign training for 200
individuals, with 20 different modules (fixed dates) and take into
account holidays. So for instance Joe Bloggs needs to attend Module A,
B & D over the next 3 months, but he also has 2 weeks holiday booked.
There are four other people in his department but I can only allow two
people at any one time to attend. It sounds like one of those Logic
Problems, and thats where I fall seriously short. I am drawing a
balnk. I was hoping someon on this group could give me some pointers
on what to do as I am rubbish with formulas etcor know where I can
download a template which may help me out here? I know what I want as
an end result but not sure which order I need to imput it into Project,
please help?


Thanks - Scarlet

 

[Harlan Grove]

scarlet wrote...

I have been tasked to organise training for my company and they have
asked me to use MS Project, which has totally blown my mind. Having
done some investigating Project will not give me the answers that I
need surprisingly, but I am hoping Excel will

....

Spreadsheets are not particularly good at slotting multiple individuals
into multiple classes on different dates. It can be used to do this,
but only in the same way as using paper & pencil. A database like
Access would be far better suited to this sort of task.

That said, you need to find out whether you can use anything other than
Project. If not, you're wasting time. While it may be easier for you to
use something else (and, IMO, Access would be better for this than
Project or Excel), your boss likely wants something that s/he or
someone else could follow if you were hit by the proverbial bus.

If you insist on using Excel, at least follow an Access-like,
table-driven approach. The starting point is creating a table of
available dates, which could be done as simply as entering Date in cell
A1 of one worksheet, then =TODAY() in A2, =A2+1 in A3, filling A3 down
as far a needed (perhaps through 12/31/2006 - about 18 months from
now), then selecting all of col A, Edit > Copy, Edit > Paste Special as
values on top of itself to make the dates constants. Then add formulas
like

B2:
=TEXT(A2,"ddd")

and fill down to match up with the dates in col A, then autofilter cols
A and B, setting a Custom Filter for col B to begins with S to filter
out Saturdays and Sundays, then select the filtered cells and delete
them (not clear them, so Edit > Delete, not Edit > Clear). Then remove
the autofilter. Delete col B and go through col A deleting holidays.
You'll be left with a table of available course dates.

Next, create a course table. If there are 20 different units, and each
unit were offerred, say, on 6 different dates, Enter Unit in cell B1 of
another worksheet, enter Unit01 in B2:B7, Unit02 in B8:B13, etc., then
enter Date in C1, and enter course dates in col C beginning in cell C2.
I've left col A for a key field. Enter UnitKey in cell A1, then

A2:
=B2&"."&IF(B2=B1,RIGHT(A1,1)+1,1)

Fill A2 down to match up with the Unit entries in col B. Use Edit >
Copy, Edit > Paste Special as values to convert col A to values. Add a
4th field, with Location in cell D1 and location IDs in col D beginning
with cell D2.

Next, create a location table with location ID in col A and location
description in col B. The location IDs in col A could be as simple as

A2:
=TEXT(ROW()-1,"\L000")

filled down then converted to constants. Use those location IDs in

Next, create an employee table with employee ID field in col A and
employee name in col B.

Finally, create an enrollment table in another worksheet. This table
would have a composite key. Col B would hold employee IDs, col C unit
keys, and col A would hold the combined key. Each combination of
employee ID and unit key should differ from all the others. Enter
EnrollKey in A1, EmployeeID in B1, UnitKey in C1. Each employee would
need to appear 20 times in col B. The EnrollmentKey would be generated
by formulas like

A2:
=B2&"_"&LEFT(C2,4)

At this point you could supplement several of these tables with
additional calculated fields. For example, add a column to the Unit
table in col E giving the number of employees scheduled for each
section of each unit using formulas like

E2:
=COUNTIF(INDEX(EnrollmentTable,0,2),A2)

and add 20 fields to the employee table giving the scheduled course
dates for each employee. If you use only col B for employee name, then
with Unit01 in C2, Unit02 in D1, etc.,

C2 [array formula]:
=VLOOKUP(VLOOKUP($A2&C$1,EnrollmentTable,3),UnitTable,3)

which would pull the dates each employee would be taking each unit.

 

[scarlet]

Wow that looks fantastic. I have printed this off and will work on it
at the weekend. I know the pain is going to be from the start but once
the sheet is set up it shoul dbe fine. At the moment I am doing it all
manually and for each individual and its taking me days to achieve
this, and when someone comes along with a reason for their inability to
attend it causes mayhem. Thank you ever so much, I really do
appreciate it. I can now walk back from the cliff edge, I think!!! :-D
If I do get a little stuck can I ask for your assistance again?