Vlookup Plus...?
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 |
Vlookup Plus...?
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!* "Ando" wrote: 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 |
Vlookup Plus...?
Thanks! It's working well.
"Luke M" wrote: 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!* "Ando" wrote: 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 |
All times are GMT +1. The time now is 06:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com