Dependent Lists for Start and End times

[jock444]

Hi, I'm creating an employee rota with Start and End times that are entered using drop down lists. I would like the End time list to change to only show times that are after the chosen Start time. For example:

Start Time List	End Time List
07:00	07:00
08:00	08:00
09:00	09:00
10:00	10:00
11:00	11:00
etc.	etc.

<tbody>
</tbody>

If 09:00 Start Time is chosen, only times from 10:00 onwards are shown for the End Time. I've been reading up on dependent lists but how to limit the second list to a certain range based on the option chosen in the first list? Any help or pointers would be greatly appreciated.

 

[DanteAmor]

In cell D2 put a drop down list with the range:
=$A$2:$A$18


In the Name Manager, create a name called "EndTime", in it refers to:
=INDIRECT("Sheet1!$B$" & MATCH(Sheet1!$D2,Sheet1!$A$1:$A$18,0) + 1 & ":$B$18")


In cell E2 put a drop down list with:
=EndTime


Check the file below
https://www.dropbox.com/s/sk6kp7a645g9jx4/time drop down list.xlsx?dl=0

Excel Workbook
	A	B	C	D	E
1	Start Time	End Time		Start Time	End Time
2	07:00	07:00		10:00	12:00
3	08:00	08:00
4	09:00	09:00
5	10:00	10:00
6	11:00	11:00
7	12:00	12:00
8	13:00	13:00
9	14:00	14:00
10	15:00	15:00
11	16:00	16:00
12	17:00	17:00
13	18:00	18:00
14	19:00	19:00
15	20:00	20:00
16	21:00	21:00
17	22:00	22:00
18	23:00	23:00
Sheet1

 

[jock444]

That's awesome, thanks a lot Dante. Is there a way of clearing the End Time (blanking the E2 cell) after a Start Time is selected as that would make it absolutely perfect for our needs.

 

[DanteAmor]

Is there a way of clearing the End Time (blanking the E2 cell) after a Start Time is selected as that would make it absolutely perfect for our needs.

Only with a macro.
To insert te code, Right click the tab of the sheet you want this to work, select view code & paste the code below into the window that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Target.Offset(0, 1).Value = ""
    End If
End Sub

 

[jock444]

Thanks Dante it works great, really appreciate your help. One final question, I've got other start and end times elsewhere on the sheet. What would be the best way of changing the macro so that it does the same thing for other cells I specify rather than just D2 (for example D2, D4 and G2)?

 

[DanteAmor]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2, D4, G2")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Target.Offset(0, 1).Value = ""
    End If
End Sub

 

[jock444]

Thanks, works great. I've realised another issue with trying to have more than one start and end time drop down list on the same sheet. The INDIRECT statement to alter the end list is for one cell/list only (D2)

=INDIRECT("Sheet1!$B$" & MATCH(Sheet1!$D2,Sheet1!$A$1:$A$18,0) + 1 & ":$B$18")

Is there a way of changing this code to make it work for other cells I specify or would it need another solution like a macro?

 

[DanteAmor]

Only copy the pair of cells (D2 and E2) in another location on the sheet and cell "D2" is set automatically in the Indirect formula.

 

[jock444]

Thanks Dante, it only seems to work when copying to the same column but I can live with that as I've found a way around it. Thanks again, you've been a great help.

 

[DanteAmor]

Use this formula:

In the Name Manager, create a name called "EndTime", in it refers to:
=INDIRECT("Sheet1!$B$" & MATCH(Sheet1!D2,Sheet1!$A$1:$A$18,0) + 1 & ":$B$18")