Data validation in 2 columns

[Tom1983]

https://chandoo.org/forum/threads/data-validation-for-2-columns.42439/HI,

Looking for solution to one problem.
Problem is -we have number of countries in different regions like Europe,Africa etc.

I have data validation in col A and Col B...when I choose any region in col A it is also showing countries in other regions as well in col. B...which we don't want.

so what is needed is...if I select Europe (Col. A) then only those countries should show up in col B which lies in Europe region.

It should apply till cell A100 and Cell B100

Not sure how to attach excel file here.

Please same question has been raised in another forum as well Chandoo.Link

But no reply received so far.

Many thanks incadvance

 

[Gerald Higgins]

Hi, this question gets asked very often on this board.

If you do a search on this board using key terms DATA VALIDATION DEPENDENT you will get several threads to help you, like this one
https://www.mrexcel.com/forum/excel...list.html?highlight=data+validation+dependent

 

[Akuini]

Not sure how to attach excel file here.

You could upload a sample workbook to a site like dropbox.com then put the link here.

I wrote a code to set up a dependent data validation, so it doesn't use formula just VBA. The code is a bit long but the set up is pretty easy.
Let me know you're interested in this approach.

 

[Tom1983]

https://www.dropbox.com/s/djpn0t55aa9k482/Test.xlsx?dl=0

Hi Akuini, Yes I am.

here is the dropbox link to file.

 

[Tom1983]

Thanks Gerald....I got answer to my post...Akuini...I guess formula will be better....it quit easy as wel.. by the way thanks for loking into it

 

[Akuini]

Ok, glad you figured it out.

 

[Tom1983]

Ok, glad you figured it out.

Sorry Akuini....I jumped to conclusion to early...it isnot working on my orginal file.

can you please provide the code.

Thank you

 

[Akuini]

Ok, try this:
If it's suitable for your project then I can explain how to set it up if you need.
Basically you just need to adjust the code in this part:
(This is in my original example, see that it has been adjusted in your sample workbook.)

'=================================================================================================
'=============== YOU MAY NEED TO ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list for data validation is located. [in the sample: sheet "sheet2"]
Private Const sList As String = "sheet2"

'table's name where the list for data validation is located. [in the sample: "Table1"]
Private Const sTable As String = "Table1"

'column number where data validation is located
Private Const sC1 As Long = 2  '[col B] for data validation 1
Private Const sC2 As Long = 3  '[col C] for data validation 2

'the helper column, first cell [if the helper column is D then it must be "D1"]
Private Const xH As String = "D1"

'the name range as the source of data validation
Private Const xN As String = "xName"

The workbook:
https://www.dropbox.com/s/ev6zerm3cj11jy0/Test(2).xlsm?dl=0