We help IT Professionals succeed at work.
Private
Troubleshooting Question

Drop down data?

Tom M
Tom M asked
on
27 Views
Last Modified: 2020-11-16
Payroll question.xlsxAs a user enters time starting at M7 is it possible to get the N7 to start at the next time stamp.  So for example if 7:00 is entered in M7 the first time in the drop down for N7 would be 7:30. if 7:30 was then entered in N7 M7's first drop down would be 8:00 and so as time is being entered?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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
Paul
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
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
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

Author

Commented:
What have I done wrong? Payroll question.xlsx
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
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

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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.

Author

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions