Vlookup in data validation

[sharma9187]

Hello,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Salesperson[/TD]
[TD]Customer Name[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Customer1[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Customer2[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD]Customer3[/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD]Customer4[/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD]Customer5[/TD]
[/TR]
</tbody>[/TABLE]

I have the above master data and I need to use data validation to get the list of customers in a drop down associated for a particular sales person. Assume I select Person A in A1 I should get the list of customers associated with Person A in B1 and that should be a drop down list using data validation.

Could anyone please help on formulating this. I have tried many options but failed.

Thanks

 

[sharma9187]

Hello,

Can I get some expert guidance on this issue please?

Thanks in advance.

 

[L. Howard]

Not an expert but try this where you will adjust ranges/cells to suit your sheet.

Paste the code in the sheet module.

Salesperson list in column A
Customer Names in column B

Data Validation drop down in cell F1 > List > Source window > =OFFSET($D$2,0,0,COUNTA($D$2:$D$200),1) > OK.

Enter new Salesperson in cell C1.

Customer names are listed in column D which the formula in the F1 Data Validation uses for its source.

Each new entry in C1 clears the list in column D and cell F1 and activates cell F1.

Regards,
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1")) Is Nothing _
   Or Target.Count > 1 Then Exit Sub

Dim j As String
Dim Lrow As Long
Dim aRng As Range
Dim c As Range

j = Range("C1")

Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Set aRng = Range("A1:A" & Lrow)

Range("D2:D200, F1").ClearContents

For Each c In aRng
  If c = j Then
    c.Offset(, 1).Copy Range("D" & Rows.Count).End(xlUp)(2)
  End If
Next
Range("F1").Activate
End Sub

 

[sharma9187]

Hello Howard,

You are a masterpice. It works like charm. Alternatively, I managed to Grab the list using different method and I will change that to the one you suggested.

The Alternative what I used was the following function.

SEARCH,ISNUMBER, MAX, IF, VLOOKUP AND ROWS FUNCTIONS.

Once again thank you.

Not an expert but try this where you will adjust ranges/cells to suit your sheet.

Paste the code in the sheet module.

Salesperson list in column A
Customer Names in column B

Data Validation drop down in cell F1 > List > Source window > =OFFSET($D$2,0,0,COUNTA($D$2:$D$200),1) > OK.

Enter new Salesperson in cell C1.

Customer names are listed in column D which the formula in the F1 Data Validation uses for its source.

Each new entry in C1 clears the list in column D and cell F1 and activates cell F1.

Regards,
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1")) Is Nothing _
   Or Target.Count > 1 Then Exit Sub

Dim j As String
Dim Lrow As Long
Dim aRng As Range
Dim c As Range

j = Range("C1")

Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Set aRng = Range("A1:A" & Lrow)

Range("D2:D200, F1").ClearContents

For Each c In aRng
  If c = j Then
    c.Offset(, 1).Copy Range("D" & Rows.Count).End(xlUp)(2)
  End If
Next
Range("F1").Activate
End Sub

 

[L. Howard]

Thanks for the feed back, glad it works for you.

Regards,
Howard

 

[KMacauley]

Hi Sharma,

I know you already have an answer, but I found this link a year or so ago which has helped me do the same thing without the use of VBA.

A Dynamic Dependent Drop Down List in Excel | Excel Semi-Pro


Kim.

 

[L. Howard]

Hi Kim,

I gave the dependent drop down method some thought and it seemed not quite what the OP was requesting.

The Salesperson and the Customer Name are both in a mixed list that requires some separating first before used in a DV drop down.

If the lists were more unique, if that is the correct word, I would have offered this example. (Only uses code to clear second drop down entry when first drop down category is re-selected.)

https://www.dropbox.com/s/dmg9ufnvyktayim/Drop Down Dependent Example Drop Box.xlsm

Regards,
Howard

 

[shinedown]

Hi Howard,
Thanks for posting this code. I am dealing with a similar issue and was wondering if you could help. This code works if the input value is in cell C1. What if I wanted this code to run for any values that are selected/entered in cell C2, C3, ...., C100, exactly the same way that it runs for a value entered in cell C1. What changes do I make to this code? Thanks.

Not an expert but try this where you will adjust ranges/cells to suit your sheet.

Paste the code in the sheet module.

Salesperson list in column A
Customer Names in column B

Data Validation drop down in cell F1 > List > Source window > =OFFSET($D$2,0,0,COUNTA($D$2:$D$200),1) > OK.

Enter new Salesperson in cell C1.

Customer names are listed in column D which the formula in the F1 Data Validation uses for its source.

Each new entry in C1 clears the list in column D and cell F1 and activates cell F1.

Regards,
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1")) Is Nothing _
   Or Target.Count > 1 Then Exit Sub

Dim j As String
Dim Lrow As Long
Dim aRng As Range
Dim c As Range

j = Range("C1")

Lrow = Cells(Rows.Count, "A").End(xlUp).Row
Set aRng = Range("A1:A" & Lrow)

Range("D2:D200, F1").ClearContents

For Each c In aRng
  If c = j Then
    c.Offset(, 1).Copy Range("D" & Rows.Count).End(xlUp)(2)
  End If
Next
Range("F1").Activate
End Sub

 

[L. Howard]

Hi shinedown,

See if this works for you.

Change this line:

If Intersect(Target, Range("C1")) Is Nothing _
   Or Target.Count > 1 Then Exit Sub

To this:

If Intersect(Target, Range("C:C")) Is Nothing _
   Or Target.Count > 1 Then Exit Sub

Regards,
Howard

 

[shinedown]

Thats doesn't seem to work like I would want it to. Basically, I am only concerned with column C and F. When a value is entered in C1, F1 populates like it did in the original code - if a value is entered in C2, F2 should do for C2 what F1 did for C1 - and so on for C3/F3, C4/F4. Thanks!