Vlookup Plus...?

[Ando]

Hi,

I wish to create a roster tool for managers (who are very basic Excel users)
to plan the shifts of workers. There are a selection of standard shifts but
non-standard shifts are often needed. The required shifts change each pay
period.

Sheet 2 is called Shifts. Row 1 columns A to L is as follows
Shift StartTime FinishTime MealBreak HrsPaid Mon Tue Wed Thu Fri Sat Sun
Row 2 and following will have the ShiftName in column A and then the detail
of the standard shifts across the row.

Sheet 1 is called Roster. Row 1 columns A to L is as follows
Shift StartTime FinishTime MealBreak HrsPaid Mon Tue Wed Thu Fri Sat Sun
The intention is the ShiftName will be entered in column A and the detail
will be populated in columns B to L. Vlookup seems to be the answer and that
is fine.

But, how can users enter a non-standard shift? ie they enter the detail
themselves?
Plus, how can users then change the non-standard shift back to a standard
shift?

Please help! (I do not use VB so if that is the answer please take it slowly)

Thanks, Ando

 

[Luke M]

You might be able to use a worksheet change event, provided your managers
aren't making large changes. This macro will clear out the formulas if the
manager inputs "Non-Standard" into column A.
Else, it will populate the row with the correct formulas.

To install:
Right-click on sheet tab, view code, paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
'check to see if Change was made to column A,
'and that user did not simply clear cell,
'and that only one cell was changed
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

'What happens when non-standard shift is created:
Application.EnableEvents = False
If UCase(Target.Value) = "NON-STANDARD" Then
Range(Cells(Target.Row, "B"), Cells(Target.Row, "L")).ClearContents
Else
For x = 2 To 12
Cells(Target.Row, x).FormulaR1C1 = _
"=VLOOKUP(RC1,'Shifts'!C1:C12," & x & ",FALSE)"
Next
End If
Application.EnableEvents = True

End Sub

 

[Ando]

Thanks! It's working well.

You might be able to use a worksheet change event, provided your managers
aren't making large changes. This macro will clear out the formulas if the
manager inputs "Non-Standard" into column A.
Else, it will populate the row with the correct formulas.

To install:
Right-click on sheet tab, view code, paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
'check to see if Change was made to column A,
'and that user did not simply clear cell,
'and that only one cell was changed
If Intersect(Target, Range("A:A")) Is Nothing Or _
Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

'What happens when non-standard shift is created:
Application.EnableEvents = False
If UCase(Target.Value) = "NON-STANDARD" Then
Range(Cells(Target.Row, "B"), Cells(Target.Row, "L")).ClearContents
Else
For x = 2 To 12
Cells(Target.Row, x).FormulaR1C1 = _
"=VLOOKUP(RC1,'Shifts'!C1:C12," & x & ",FALSE)"
Next
End If
Application.EnableEvents = True

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*