Drop down data?

Right-click your worksheet tab and select View code. Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("M7:M28")

  If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        
   Range("N" & Target.Row).Value = Target.Value + 0.02083333
   
  End If
  
End Sub

Select allOpen in new window

Paul

VBA is not required for this request.

The trick is to create two named ranges StartTimes and FinishTimes on each of the worksheets.

To create Tuesday!StartTimes, select Tuesday cell M7 and use the Formulas...Name Manager ribbon item to create the named range with the formula:

=INDEX(Driver!$L$3:$L$50,MATCH(Tuesday!$N6,Driver!$L$3:$L$50,0)):Driver!$L$50

Select allOpen in new window

You may then use that in cell M7 data validation with the source formula =StartTimes

To create Tuesday!FinishTimes, select cell N7 and create the named range with the formula:

=INDEX(Driver!$L$3:$L$50,MATCH(Tuesday!$M7,Driver!$L$3:$L$50,0)+1):Driver!$L$50

Select allOpen in new window

You may then use that named range in cell N7 data validation with the source formula =FinishTimes

Because the named ranges have worksheet scope, you'll need to create them for each worksheet Sunday through Saturday. The easy way to do that is to copy Tuesday six times, then rename those worksheets as desired.

Payroll-questionQ29198670.xlsx

What have I done wrong? Payroll question.xlsx

Can you think of a reason why the drop down box might disappear when I share this file in sharepoint.  What is happening is say i enter 1:00  as start time in k7 the l7 has the drop down and things work fine, but if say I enter 7:00 as the startime in k7 the i7 drop down disappears

Could you please post a file that reproduces this problem? The file I posted has Start time in column M and Finish time in column N, so I know you made some changes.

Here is the file, it seems to work just fine until I place the file in Sharepoint that is when the drop down times mysteriouslyPayroll Question.xlsx disappear