conditional formating with drop-down list

[cindyburrows]

I am wondering if there is any way to do something. I am creating a
schedule. I have created a list of employee shifts that need to be
filled every day. I have also made a schedule with all of my employees
names going down one column and the dates (Mon.-Sun.) going across.
Next I inserted drop-down menus, using the shift lists, for each day
and each employee, showing the shifts available for that day. Is is
possible to make the shifts that are already being used for each day
either change color or not show that they are available any longer? It
would be a way to ensure that shifts are not being double scheduled or
totally missed. There are about 40 employees and 15-25 shifts
depending on the day. Is this possible? Does it make any sense?!

 

[Bob Greenblatt]

If you want the items in the drop down list to appear in color when the list
is "dropped down", I'm afraid not. It's possible to do something in VBA code
but not to color the list. Code could remove already chosen items from the
list. It might be a bit tricky if someone "unchose" a shift to get it back
in the list, but certainly possible. This is not a trivial macro. You
probably also need something to show that all possible shifts have indeed
been scheduled.

 

[JE McGimpsey]

I am wondering if there is any way to do something. I am creating a
schedule. I have created a list of employee shifts that need to be
filled every day. I have also made a schedule with all of my employees
names going down one column and the dates (Mon.-Sun.) going across.
Next I inserted drop-down menus, using the shift lists, for each day
and each employee, showing the shifts available for that day. Is is
possible to make the shifts that are already being used for each day
either change color or not show that they are available any longer? It
would be a way to ensure that shifts are not being double scheduled or
totally missed. There are about 40 employees and 15-25 shifts
depending on the day. Is this possible? Does it make any sense?!

it makes sense, and I'm sure it can be done, but I'm not sure from your
description how XL should determine that a shift is available or taken.

An alternative method, assuming your dropdowns are generated using Data
Validation, is to have taken shifts hidden on the shift list. See Debra
Dalgleish's method here:

http://contextures.com/xlDataVal03.html