Select name from drop down and return associated value

[DebbieV]

Hi
I have a spreadsheet where I need to return a value which is an
alphanumerical code but the users know only the name.

Example:

In worksheet A there are details of clients. If the clients
participate in a course we are required to record the course code not
the name.

Column C is where I want the value to appear. What i would like to
occur is that the user selects from a drop down list the name of the
course but on selection the value returned is the course code.

Is this possible?

 

[Otto Moehrbach]

Debbie
Are you saying that you want the user to select from a dropdown some
course name, but you want Excel to display the course code in that same cell
and not the course name selected? If so, yes, this can be done with VBA
(programming). I take it that your workbook has a list of course names and
matching course codes?
The following macro will do that. I assumed that your list of course names
is in Column A of a sheet named "List" starting in A2 and the corresponding
course codes are in Column B of the same sheet. Note that your setup of the
Data Validation cell must include to not display an error when a "wrong"
entry is made. Note that this macro must be placed in the sheet module of
the sheet that contains the Data Validation cell. Right-click on that sheet
tab and select View Code. Paste this macro into that module. "X" out of
the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Sheets("List")
Application.EnableEvents = False
Range("A1") = .Range("CourseNames"). _
Find(What:=Target, LookAt:=xlWhole).Offset(, 1)
Application.EnableEvents = True
End With
End If
End Sub

 

[Don Guillett]

Only slightly tighter
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address _
Or Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Application.VLookup(Target, [coursenames], 2, 0)
'OR
'Target = Range("CourseNames"). _
'Find(Target, LookAt:=xlWhole).Offset(, 1)
Application.EnableEvents = True
End Sub

 

[Otto Moehrbach]

Thanks Don. It's always good to see another view of it. Otto

Only slightly tighter
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address _
Or Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Application.VLookup(Target, [coursenames], 2, 0)
'OR
'Target = Range("CourseNames"). _
'Find(Target, LookAt:=xlWhole).Offset(, 1)
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Debbie
Are you saying that you want the user to select from a dropdown some
course name, but you want Excel to display the course code in that same
cell and not the course name selected? If so, yes, this can be done with
VBA (programming). I take it that your workbook has a list of course
names and matching course codes?
The following macro will do that. I assumed that your list of course
names is in Column A of a sheet named "List" starting in A2 and the
corresponding course codes are in Column B of the same sheet. Note that
your setup of the Data Validation cell must include to not display an
error when a "wrong" entry is made. Note that this macro must be placed
in the sheet module of the sheet that contains the Data Validation cell.
Right-click on that sheet tab and select View Code. Paste this macro
into that module. "X" out of the module to return to your sheet. HTH
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Sheets("List")
Application.EnableEvents = False
Range("A1") = .Range("CourseNames"). _
Find(What:=Target, LookAt:=xlWhole).Offset(, 1)
Application.EnableEvents = True
End With
End If
End Sub

 

[DebbieV]

Only slightly tighter
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address _
Or Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
  Target = Application.VLookup(Target, [coursenames], 2, 0)
  'OR
  'Target = Range("CourseNames"). _
  'Find(Target, LookAt:=xlWhole).Offset(, 1)
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

- Show quoted text -

Hi Otto & Don

Thanks for your responses. I am quite the novice when it comes to
macros. I have tried but to no success. Is there any other way? I
think I may have to increase my knowledge if not.

cheers
Debbie

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks Don. It's always good to see another view of it. Otto"Don Guillett"

Only slightly tighter
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address _
Or Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Application.VLookup(Target, [coursenames], 2, 0)
'OR
'Target = Range("CourseNames"). _
'Find(Target, LookAt:=xlWhole).Offset(, 1)
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

- Show quoted text -

Hi Otto & Don

Thanks for your responses. I am quite the novice when it comes to
macros. I have tried but to no success. Is there any other way? I
think I may have to increase my knowledge if not.

cheers
Debbie

 

[Otto Moehrbach]

Debbie
Just jump in and get your feet wet. VBA is a very powerful tool and
you'll be thankful when you learned even a little of it. Otto

Thanks Don. It's always good to see another view of it. Otto"Don Guillett"

Only slightly tighter
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("b1").Address _
Or Target.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Application.VLookup(Target, [coursenames], 2, 0)
'OR
'Target = Range("CourseNames"). _
'Find(Target, LookAt:=xlWhole).Offset(, 1)
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

- Show quoted text -

Hi Otto & Don

Thanks for your responses. I am quite the novice when it comes to
macros. I have tried but to no success. Is there any other way? I
think I may have to increase my knowledge if not.

cheers
Debbie