Conditional Drop Down List

[Slobdell]

Hi, I'm making a employee scheduling spreadsheet. Here is my problem:

I have a list of times and corresponding values...

9:00 AM	9
9:30AM	9.5
10:00 AM	10

...that span all the working hours of the day. On the sheet where you schedule employees, I have drop down lists of available times to select. I also have a sheet where you can specify which hours each employee is available. My question is, what is the most efficient way to make the drop down list conditional based on employee availability?

For example, if Employee 1 is available from 11:00 AM to 3:00PM, how can I set the drop down list to only show times within that range? There are 10 different employee spots and I'm trying to figure out how to do this without having a different list of times for each of the ten employees, for 7 days of the week. Thanks

Steve

[jason.b75]

There is a guide that you should be able to adapt to your sheet here

http://www.contextures.com/xlDataVal02.html

[npamcpp]

Define a list of employees and add their start and end times to the table. Then you can use a defined name with a careful mix of absolute and relative referencing to create personalised drop-downs. See attached. The formula to define the range is

=INDEX(Sheet2!$A$2:$A$23,MATCH(VLOOKUP(Sheet1!$A1,Sheet2!$D$1:$E$11,2,FALSE),Sheet2!$A$2:$A$22)):INDEX(Sheet2!$A$2:$A$23,MATCH(VLOOKUP(Sheet1!$A1,Sheet2!$D$2:$F$11,3,FALSE),Sheet2!$A$2:$A$23))

On sheet1 you can see that for each employee, the drop down values for the availability is different. This is because the Vlookup in the defined name uses a relative reference for the row of the lookup value.

[ncmay]

This example uses the INDIRECT function for the data validation drop downs

[Slobdell]

Thanks you guys are awesome! Got it up and running.

Steve