Drop down options

[Capy_Uther]

How can i make this work, i made this drop down list

Its getting the information from this table

The table its on a Sheet called "Vendedores" and the validation information on the drop down menu is like this

What i want is if i choose example Mildred on the drop down menu instead of the name the cell shows "16" because is the number infront of the Mildred name on the table

 

[kweaver]

In your image, the table doesn't show a number in from of the names.
Maybe use XL2BB to show your sheet and expected result(s)!

 

[Peter_SSs]

You could try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab of the sheet that contains the Data validation cell(s) and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1. Edit the range in the 'Set' line as required. I have written it as if J2:J10 are the cells that have the Data Validation drop-downs.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Range("J2:J10")) '<- Edit range as required
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
        c.Value = Sheets("Vendedores").Range("A2:A500").Find(What:=c.Value, LookAt:=xlWhole, MatchCase:=False).Offset(, 1).Value
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[Capy_Uther]

You could try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab of the sheet that contains the Data validation cell(s) and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1. Edit the range in the 'Set' line as required. I have written it as if J2:J10 are the cells that have the Data Validation drop-downs.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Range("J2:J10")) '<- Edit range as required
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
        c.Value = Sheets("Vendedores").Range("A2:A500").Find(What:=c.Value, LookAt:=xlWhole, MatchCase:=False).Offset(, 1).Value
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

Thanks for you help sir but still showing the names on the drop down list

i have the sheet call Vendedores whit this information

I made a drop down list on another sheet and that drop down list show me the names that you can see on Vendedores sheet A2 to A7 until now everything is ok, Example what i want to do is when i click the name Charly on the drop down list, that name its on the Venderoes Sheet A4, instead of show the name Charly it shows 14 because thats the number for charly at Vendedores sheet cell B4

 

[Peter_SSs]

Then I must not be understanding what you want. As far as I can understand, my code does what you are asking here

what i want to do is when i click the name Charly on the drop down list, that name its on the Venderoes Sheet A4, instead of show the name Charly it shows 14 because thats the number for charly at Vendedores sheet cell B4

Here is my drop-down in cell J2 (but it could easily be for A4 by editing the range in the code as i described above)

Is that what you want to see in the drop-down?
If not what do you want to see?

Now, after I click on Charly, this is what I see in my worksheet.

If that is not what you want to see on the worksheet, what do you want to see?

 

[Capy_Uther]

Then I must not be understanding what you want. As far as I can understand, my code does what you are asking here


Here is my drop-down in cell J2 (but it could easily be for A4 by editing the range in the code as i described above)

View attachment 91746

Is that what you want to see in the drop-down?
If not what do you want to see?

Now, after I click on Charly, this is what I see in my worksheet.

View attachment 91747

If that is not what you want to see on the worksheet, what do you want to see?

Ohh i understand now your code but what if the drop-down its on a diferent Sheet, the table with names and numbers its on Sheet "Vendedores" but the Drop-Down its on a Sheet called pag1 on the cells N2 and O2 because the drop-down use 2 columns as you can see on the next image

 

[Peter_SSs]

what if the drop-down its on a diferent Sheet, .. the table with names and numbers its on Sheet "Vendedores but the Drop-Down its on a Sheet called pag1"

That is how mine is too. There is no issue with that.

on the cells N2 and O2

So simply change the range in the code as I suggested before.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Range("N2:O2")) '<- Edit range as required
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
        c.Value = Sheets("Vendedores").Range("A2:A500").Find(What:=c.Value, LookAt:=xlWhole, MatchCase:=False).Offset(, 1).Value
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub