How can I get Excel to prevent me from making mistakes, when choosing from a list?

[Ray]

Situation: I have been given a new job, at work, where I am now in
charge of the work schedule, scheduling in over fifty employees' work
times. The employees' numbers and names are listed in two columns
that are side-by-side: column A contains the employees' numbers, and
column B contains their names. These columns are in ascending order,
according to when the employees are hired, so the recently-hired ones
are at the bottom of the two columns, and the previously-hired
employees are at the top of the columns; no alphabetical order, here.
Coumn C contains the hire dates of the employees; this column in in
ascending order, with the newly hired employees at the bottom,
previously-hired ones at the top, same as columns A & B. Column D,
labeled "Job Description," lists their jobs ("Full Time Cashier,"
"Part Time Cashier," "Full Time Assistant," "Part Time Assistant,"
etc. All of these columns are on a worksheet named "Employee
Roster."

On different worksheets (ones labeled "Mon.," "Tue.," etc.), I need to
be able to schedule in people for different time slots, for each day:
e.g., on the "Mon." worksheet, I need to be able to select a worker
named "Cindy" to start work at 9:00 am, then another named "Alena," at
10:00, etc. To do this, I select the employee who I am trying to
schedule by going to the "Employee Roster" worksheet and choosing
their employee number, then type it into the cell on "Mon."s
worksheet; e.g., I would go to the "Employee Roster" worksheet, choose
Cindy's employee number, then type that number into the appropriate
cell, on "Mon."s worksheet cell. Nothing difficult, so far.

Problem: The employees usually want the earlier shifts, so they can
get off earlier in the day. So, to be fair, we schedule those
employees who have seniority for the earlier shifts than the newly-
hired ones. However, it's real easy to make a mistake on this. If I
mistakenly schedule an employee who has seniority later in the day
than one who was hired more recently, the one with seniority gets
really mad, and comes complaining to me....stress! How can I get
Excel to look at the list of employees, and prevent me from putting
them in the wrong order?

Problem on the Problem: Here's the real complicating factor (for me,
anyway, as I'm a newbie to Excel). Of course, not all of the
employees have the same job; some are full-time cashiers, some are
part-time cashiers, some are full-time assistants ("box boys/girl"),
and some are part-time assistants ("box boys/girls"). How do I get
Excel to prevent me from mistakenly scheduling not only a newly-hire
ahead of one with seniority, but also to prevent me from scheduling,
say, a part-time Cashier, when I should've scheduled a full-time
cashier? The full-time cashiers would, understandably, be very upset,
if I started to schedule the part-time cashiers in, and not them.

Did all of this make sense? If you have read this far, and would be
willing to tackle it for me, then you are a saint! I have recently
made several mistakes in doing the schedule, and it is giving me a lot
of stress that I am taking home and worrying over to the point of
being sick.

 

[Charlie]

Sure it makes sense, but this forum is mostly for answering a few simple
syntax questions not writing an entire application. Someone may take a shot
at it though, if you've got the time to wait.

If not, you may be less stressed out looking for a time-scheduling
application to purchase, or hire a contract programmer for a couple of weeks.
It might be worth the cost.

 

[txilya]

Ray, you can achieve your objective without VBA programming; simply use
Excel's AutoFilter.
On the "Employee Roster" worksheet, select column D header - "Job
Description", then select Data>Filter>AutoFilter. Your employees are already
sorted by seniority, so all you need to do is select desired job position
using the filter. Then simply copy filtered employee numbers (col. A) and
names (col. B) from the roster and paste the data on desired sheet. Repeat as
needed for other job positions.
Ilya

 

[Randy Harmelink]

I think what I would do is expand the employee roster worksheet to
have all the fields you need to do the scheduling, then generate the
daily schedules from that database. That way, you're doing all of
your scheduling in one place, so it should be easier to do the
scheduling process.

For example, if your "database" contained fields like:

-- Employee Number
-- Employee Name
-- Hiring Date
-- Job title
-- Part/Full time
-- Monday Start/End Time (or whatever is needed)...
-- Tuesday Start/End Time...
.....etc

Then you just need the "Monday" worksheet to extract and format the
necessary data out of that "database". You could sort that database
easily, as needed -- for example, to keep them in seniority order.
And you could use the "Filter" process txlya else mentioned to
restrict the database to "Cashiers" or "Assistants" when you're doing
each, just to make the process a little easier scheduling each job
category for the week. You may even be able to get by with JUST the
database, depending on how you have to post the schedule -- it may be
able to be formatted for printing as-is? Plus, you wouldn't need to
be retyping employee numbers between sheets, or constantly hopping
between worksheets when creating the schedule.

Of course, the devil is always in the details...but I think you're
generally better off with the data storage and maintenance being done
in one place in a consistent format. It usually gives you more
flexibility for manipulating the data later on.

 

[Ray]

Ray, you can achieve your objective without VBA programming; simply use
Excel's AutoFilter.
On the "Employee Roster" worksheet, select column D header - "Job
Description", then select Data>Filter>AutoFilter. Your employees are already
sorted by seniority, so all you need to do is select desired job position
using the filter. Then simply copy filtered employee numbers (col. A) and
names (col. B) from the roster and paste the data on desired sheet. Repeat as
needed for other job positions.
Ilya









- Show quoted text -

Thank you, Txilya, for that suggestion. I will give it a try. I have
never used filters before, but they sound like what I might be looking
for. Very helpful suggestion.

 

[Ray]

I think what I would do is expand the employee roster worksheet to
have all the fields you need to do the scheduling, then generate the
daily schedules from that database. That way, you're doing all of
your scheduling in one place, so it should be easier to do the
scheduling process.

For example, if your "database" contained fields like:

-- Employee Number
-- Employee Name
-- Hiring Date
-- Job title
-- Part/Full time
-- Monday Start/End Time (or whatever is needed)...
-- Tuesday Start/End Time...
....etc

Then you just need the "Monday" worksheet to extract and format the
necessary data out of that "database". You could sort that database
easily, as needed -- for example, to keep them in seniority order.
And you could use the "Filter" process txlya else mentioned to
restrict the database to "Cashiers" or "Assistants" when you're doing
each, just to make the process a little easier scheduling each job
category for the week. You may even be able to get by with JUST the
database, depending on how you have to post the schedule -- it may be
able to be formatted for printing as-is? Plus, you wouldn't need to
be retyping employee numbers between sheets, or constantly hopping
between worksheets when creating the schedule.

Of course, the devil is always in the details...but I think you're
generally better off with the data storage and maintenance being done
in one place in a consistent format. It usually gives you more
flexibility for manipulating the data later on.



- Show quoted text -

So, you're saying to just put everything on the same worksheet. That
certainly would make things easier, as I wouldn't have to be
constantly refering back to the employee roster, when I schedule each
day. Thank you for that idea.