3 level of drop down or dependent list

[ashwithonline]

Hi All,

In the attached excel, I have 3 levels of data which are dependent on each other.

Also I have added column for all the 3 levels of data. Now I want to connect all three levels,

1st would be to select the country, then the states of that country would come and after that the cities of that state would come.


Thanks all in advance,
Jagdish

 

[Gateway2026]

Hi,
Create Named Ranges
Formulas → Name Management → New for Each:
Country=Sheet1!$C$2:$C$4
India=Sheet1!$D$2:$D$5
United_States=Sheet1!$E$2:$E$5
Europe=Sheet1!$F$2:$F$4
Maharashtra=Sheet1!$G$2:$G$5
Karnataka=Sheet1!$H$2:$H$5
Kerala=Sheet1!$I$2:$I$5
Gujarat=Sheet1!$J$2:$J$5
Los_Angeles=Sheet1!$K$2:$K$5
California=Sheet1!$L$2:$L$5
Texas=Sheet1!$M$2:$M$6
Georgia=Sheet1!$N$2:$N$6
Germany=Sheet1!$O$2:$O$7
France=Sheet1!$P$2:$P$6
Italy=Sheet1!$Q$2:$Q$5

after select;

Add Data Validation
Select E21 → Data → Data Validation:
Allow: List
Source: =Country

Select F21 → Data → Data Validation:
Allow: List
Source: =INDIRECT(SUBSTITUTE(E21," ","_"))

Select G21 → Data → Data Validation:
Allow: List
Source: =INDIRECT(SUBSTITUTE(F21," ","_"))

 

[Peter Bartholomew]

I did some exploring with this to see whether 365 provides any non-traditional approaches. I come up with the idea of using Lambda functions to reference the range of eligible values (cities or states/regions).

REGIONλ = LAMBDA(country,
    TRIMRANGE(XLOOKUP(country, countryHdrs, regionLists))
);

CITYλ = LAMBDA(region,
    TRIMRANGE(XLOOKUP(region , regionHdrs, cityLists))
);

so the data validation formulae were based upon

regions
= VALIDATION.REGIONλ(countrySelected)

cities
= VALIDATION.CITYλ(regionSelected)

(There are probably some unused names that were created in passing)