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.

In linked thread I posted worksheet where on sheet Report persons are selected depending on previously selected organization. Depending on organization selected, on sheet Hidden a list of persons from this organization is calculated. This list is used as Data Validation source to select person.

https://www.msofficeforums.com/excel...oint-data.html

Thanks for your reply. i downloaded the example you pointed me to. However, the example spreadsheet seems to have the data available in multiple tabs (tables). I would love to filter the data based on 1 table (2 columns, continuing rows) but not sorted... hope what i am saying here makes sense

This example worksheet was created for another purpose, cascaded validation lists were a byproduct. You can ignore all except:
1. On sheet Persons columns PersonName and PersonOrganization. This matches with your Continent-Country list.
2. On sheet Organizations column OrgName. You have to replace this with list of continents. As this list hardly changes ever, you can later hide the sheet with continents list, or you can enter the list of continents directly as data validation list source, and drop the Continents sheet at all. But this may mean more work when designing the application.
3. Sheet Hidden is essential, as here the list of persons (countries) belonging to selected organization (continent) is generated. as the name suggests, this sheet is meant to be hidden from users. NB! This example was created to select organization and person in 2 cells! For cascading data validation lists used in tables, slightly different approach is needed - on this hidden sheet is calculated a table with a column for every continent, and into every column a list of countries from this continent. And for table where you want to select country for selected continent, you have to define a dynamic named range which returns the list of countries for selected continent from proper column of this Hidden sheet. In your opening post you did write "two input field" - when you really want two table columns, then let me know.

You'll have a sheet with list of countries by continent and your main sheet where you select continent and country visible. You can't have those 2 in one and have any working formulas!. And as minimum you'll have one hidden sheet, which must not bother you at all.

Hi

Why make life difficult for yourself by continuing with a two column unsorted table?
You could easily extract a list of countries belonging to each Continent to create 5 individual tables.
Then you can achieve what you want very easily as described in a Tutorial I posted here
http://www.contextures.com/exceldata...sindirect.html