Can this Be Done in Excel ?

[jmacvicar]

I have a employee job scheduling table that I would like to populate
based on criteria from 2 other tables.

The scheduling table has the employee names listed in each row and
across the top is the period of the day broken into 4 time periods.


The critera tables are:


1. A training matrix that lists the employee names in each row and
the
jobs listed across the top. If an employee is trained, the
intersecting cell has a "T". If they are not trained, the cell is
blank.


2. An attendance table that lists the employee names in each row and
if they are absent, there is an "A" in the next cell. If they are at
work, the cell is blank.


I'd like to be able to populate the scheduling table based on whether
they are trained and present at work. Basically, each day the
attendance table would be updated and then the scheduling table would
be automatically re-calculated. There would need to be provisions to
prevent the same employee from being scheduled on the same job more
than once a day on a certain job unless no one else was trained.


Can this be setup in excel ?


Thanks


Jeff

 

[Guest]

Jeff

You need someone to write a macro for you, if I could program Sudoku then
maybe I could do this. In the mean time here is something to help you.

I had my table of jobs and employees on one sheet.

Table 1
Names Job1 Job2 Job3 Job4
Jon T T
Joe T T T
Ali T T
Jean T T
Fred T T T
Alice T

Table 2
Names In
Jon A
Joe
Ali
Jean A
Fred
Alice

TAble 3 on sheet2

Schedule1 Job1 Job2 Job3 Job4 Tasks Avail
Alice 1 0 0 0 1
Ali 0 0 1 1 2
Joe 1 1 0 1 3
Fred 1 1 0 1 3
Jon 0 0 0 0
Jean 0 0 0 0
Total1 3 2 1 3

the formula in B1 Job1 (Alice)
=SUMPRODUCT(--(Sheet1!$A$3:$A$8=$A2),(Sheet1!B$3:B$8="T")*(Sheet1!$B$12:$B$17<>"A"))

copy this across and down

formula in F1
=IF(SUM(B2:E2)=0,"",SUM(B2:E2))
and copy down

I then sorted the list on column F and used this to produce a schedule below.

I started the schedule in A11 and used the formula =A1 for the names which
helps when sorting.

Will this do for now?

Peter

 

[jmacvicar]

Jeff

You need someone to write a macro for you, if I could program Sudoku then
maybe I could do this. In the mean time here is something to help you.

I had my table of jobs and employees on one sheet.

Table 1
Names Job1 Job2 Job3 Job4
Jon T T
Joe T T T
Ali T T
Jean T T
Fred T T T
Alice T

Table 2
Names In
Jon A
Joe
Ali
Jean A
Fred
Alice

TAble 3 on sheet2

Schedule1 Job1 Job2 Job3 Job4 Tasks Avail
Alice 1 0 0 0 1
Ali 0 0 1 1 2
Joe 1 1 0 1 3
Fred 1 1 0 1 3
Jon 0 0 0 0
Jean 0 0 0 0
Total1 3 2 1 3

the formula in B1 Job1 (Alice)
=SUMPRODUCT(--(Sheet1!$A$3:$A$8=$A2),(Sheet1!B$3:B$8="T")*(Sheet1!$B$12:$B$­17<>"A"))

copy this across and down

formula in F1
=IF(SUM(B2:E2)=0,"",SUM(B2:E2))
and copy down

I then sorted the list on column F and used this to produce a schedule below.

I started the schedule in A11 and used the formula =A1 for the names which
helps when sorting.

Will this do for now?

Peter

Hi Peter,

This kind of works. However, the format of the schedule I'd like to
end up with is different. Here's what I have:

Table 1 - Training Matrix (Employees in Col A and Job Names in row 3)
A "Y" means that can work that job.

Job 1 Job 2 Job 3 Job 4 Job 5
Phil Y Y Y Y
Bill Y Y Y Y
Joe Y Y Y Y
Dan Y Y Y Y Y
Tim Y Y Y Y


Table 2 - Attendance - An "A" means they are absent & cannot be
scheduled

Phil
Bill
Joe A
Dan
Tim

Table 3 - Schedule

7am-9am 9am-11am 11am-1pm 1pm-3pm
Phil Job 1 Job 2 Job 4 Job 5
Bill Job 3 Job 4 Job 5 Job 1
Joe
Dan Job 2 Job 3 Job 1 Job 4
Tim Job 5 Job 1 Job 3 Job 2

Thanks for your help.

Jeff

 

[Guest]

Billy,

Can I get the macro for the sudoku solver? I have one puzzle unable to get
and I think the solver would come in handy. Thanks.

 

[ilia]

I have a macro for sudoku solver, but it uses a recursive guess-and-
check strategy, not actually "solving" it.

If you want it, let me know.

Billy - how does yours work?

 

[Guest]

Sounds like a plan. Still would like a solver though. My addy should be in
the profile. Thanks.

Do I need a puzzle template to put the known numbers in?

 

[Guest]

I can't find it. Why don't you e-mail me and I'll write back.