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

 

[L. Howard]

Hi shinedown,

How about posting a link to an example workbook along with detailed what you want and where.
I use Drop Box, but there are others as well.

With that and I think the code can be adjusted to suit.

Howard

 

[shinedown]

Hi Howard,
Here is the link to an example workbook.
https://www.dropbox.com/s/o8dcdb54j63z8ax/Example.xlsm
Sheet2 contains Customer and Address data. What I am looking for is that if a user enters a Customer in Cell A1 of Sheet1 then cell B1 in Sheet1 is populated by a dropdown list of all addresses in column B of Sheet2 that pertains to the customer entered in cell A1 of Sheet1. I want a similar list populated in cell B2 of Sheet1 when a customer is entered in cell A2 of Sheet1, and so on.
Many thanks for your help.

Hi shinedown,

How about posting a link to an example workbook along with detailed what you want and where.
I use Drop Box, but there are others as well.

With that and I think the code can be adjusted to suit.

Howard

 

[L. Howard]

Hi shinedown,

A few questions:

1.
On sheet 2 you have seven Customer A's and 7 different address.
Is each "A" a different customer name or are all the "A"s the same name?
Same question with the B's and C's.

2.
Are you flexible with the data layout on sheet 2 or does it all have to be in column A and B?

3.
Will you be adding and/or deleting customers and address from the various A's, B's and C's etc. lists?

4.
With the many drop downs in column A of sheet 1, am I to assume that you may want to have any customer chosen in any drop down and the proper address's will be be the choices.
For example Customer Z may be selected in the drop down in A1 and Customer A may be selected in A18.

Howard

 

[shinedown]

Please see my responses below. Many thanks.

Hi shinedown,

A few questions:

1.
On sheet 2 you have seven Customer A's and 7 different address.
Is each "A" a different customer name or are all the "A"s the same name?
Same question with the B's and C's.
>> Same customers but different addresses.

2.
Are you flexible with the data layout on sheet 2 or does it all have to be in column A and B?
>>They will most likely be in Columns CM and CN but if we can make them work for columns A and B, I can change the code accordingly to make it work for any other two columns.
3.
Will you be adding and/or deleting customers and address from the various A's, B's and C's etc. lists?
Yes, that list will change.

4.
With the many drop downs in column A of sheet 1, am I to assume that you may want to have any customer chosen in any drop down and the proper address's will be be the choices.
For example Customer Z may be selected in the drop down in A1 and Customer A may be selected in A18.
>> Yes.

Howard

 

[shinedown]

The following formulae in data validation did the trick for me. Thanks much for all the input.

=OFFSET(Tables!CM1, MATCH(AO12,Tables!CM$2:CM$20000,0),1,COUNTIF(Tables!CM:CM,AO12),1)

 

[L. Howard]

Okay, you got it going, that's good.


Regards,
Howard