Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |