Hi Excel Masters and Gurus,

new to the forum, a big warm "hi" to everyone.

i have been struggling int he past few weeks to build a sound "simple" data validation rule helps the user to enter data in a controlled fashion.

in a nutshell, i have got a reference table of two columns, Continent and Country. The data in these two columns are not sorted.





Column A Column B
Continent Country
Africa Angola
Africa Egypt
Asia Japan
Europe UK
America Canada
Europe France
Europe Italy
Oceania Australia
Asia Korea
Oceania NZ
America USA


Africa Kenya

what i love to create is a two input field,

after user enter the continent field, the next field automatically filters out the options based on the reference list. Say enter "Asia" in range D4 (as shown in picture), Range E5 has a drop down list of "Japan" and "Korea" only. Also, if an invalid data of "Continent" entered in D4, the drop down list in E5 shows "No valid data found"

i have seen quite few tutorials about utilizing OFFSET or Indirect Function, but all seem to need either the reference table to be sorted or transformed into a single column table.

Any thoughts?

Much appreciate your response.