Vlookup in data validation

sharma9187

New Member
Joined
Jul 11, 2012
Messages
8
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
 
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top