Validation List to return another value

[teri_carlson]

How can i do the following with out creating another collum

I know how to make a list using validation but i would like it to return a different value

I actually want it to retun the abrevation

so when they veiw the list to pick from they would see

Regular Time
Over Time (1.5)
Double Time
Absent
Late Arrival
Approved Time Off

what i would like it to return would be

REG
OT
DBLT
AB
LATE
ATO

lets say i would like this validation in sheet 1 C2
the name range for the list is called Type1 the range i would like it to return is named Abrev1
Type1 is in(sheet 2 A1:A15) Abrev1 is in(sheet 2 B1:B15)


Can some one help me out with this?
I hope this makes sense


I know it can be done i just cant remeber how

 

[lenze]

Just use a WorkSheet_Change event. Right click the sheet tab and choose "View Code". Enter in the white panel

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$C$2" Then Exit Sub
Application.EnableEvents = False
Select Case Target
Case "Regular Time": Target = "REG"
Case "Over Time (1.5)": Target = "OT"
'etc
Case Else:
End Select
Application.EnableEvents = True
End Sub

lenze

 

[VoG]

A similar approach

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pick, Out, i As Integer
Pick = Array("Regular Time", "Over Time(1.5)", "Double Time", "Absent", "Late Arrival", "Approved Time Off")
Out = Array("REG", "OT", "DBLT", "AB", "LATE", "ATO")
If Target.Column <> 3 Then Exit Sub
Application.EnableEvents = False
For i = LBound(Pick) To UBound(Pick)
    If Target.Value = Pick(i) Then
        Target.Value = Out(i)
        Exit For
    End If
Next i
Application.EnableEvents = True
End Sub

 

[teri_carlson]

Ok , this is the only part of the code i cant get, the lists actually start at E10 of the first sheet so what should i change this part too?

If Target.Address <> "$C$2" Then Exit Sub

 

[teri_carlson]

can you explain what the target adress is suposed to be?
basically i want it for the entire collum E

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$E$" Then Exit Sub
Application.EnableEvents = False
Select Case Target
Case "Regular Time": Target = "REGT"
Case "Over Time (1.5)": Target = "OVER"
Case "Bereavement": Target = "BELV"
Case "Jury Duty": Target = "JURY"
Case "TAS Safety Meeting": Target = "SFMT"
Case "Stewards Leadership Meeting": Target = "SLMT"
Case "Sleep Time Taken": Target = "SLTM"
Case "TAS Day of Rest": Target = "TDOR"
Case "Training Taken": Target = "TRTK"
Case "Approved Requested Time Off": Target = "ARTO"
Case "Banked Time Requested (future)": Target = "BKTR"
Case "Late": Target = "LATE"
Case "Laid Off Available": Target = "LDAV"
Case "Training Scheduled": Target = "TRSC"
Case "TAS Shift Day Off": Target = "TSDO"
Case "TAS Lieu Day": Target = "TSLU"
Case "TAS Medical Leave": Target = "TSML"
Case "TAS Personal Day": Target = "TSPR"
Case "TAS School": Target = "TSSC"
Case "TAS Sick": Target = "TSSK"
Case "TAS Stat": Target = "TSST"
Case "TAS Workers Compensation": Target = "TWCB"
Case "Absent without Approval": Target = "AWOA"

Case Else:
End Select
Application.EnableEvents = True
End Sub

 

[VoG]

Neither lenze's code nor mine depend on where the validation list is. In my code the macro will only run if the cell the validation is performed on is in column C and in lenze's it is restricted to C2.

 

[VoG]

If the validation is being performed on column E (you mentioned C2 previously) then

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pick, Out, i As Integer
Pick = Array("Regular Time", "Over Time(1.5)", "Double Time", "Absent", "Late Arrival", "Approved Time Off")
Out = Array("REG", "OT", "DBLT", "AB", "LATE", "ATO")
If Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False
For i = LBound(Pick) To UBound(Pick)
    If Target.Value = Pick(i) Then
        Target.Value = Out(i)
        Exit For
    End If
Next i
Application.EnableEvents = True
End Sub

 

[lenze]

Ok , this is the only part of the code i cant get, the lists actually start at E10 of the first sheet so what should i change this part too?

If Target.Address <> "$C$2" Then Exit Sub

If Intersect(Target,Range("$E$10:$E" & Range("$E$65536").End(xlUp).Row)) Is Nothing Then Exit Sub

lenze