Create multi level drop down validation in Excel

data_collection
excel

#1

Hi,

I am creating a report and showing metrics at three dimension level (Region, City, Outlet). Here, I want to create drop down for Region, City and Outlet and these drop down must be relative. For example: if I have selected North Region then City drop down only have city names of North and if Delhi is selected then only outlets of Delhi must be available under Outlet drop down. Please help me to solve this.

Region    City      Outlet
North   Delhi        N001
North   Jaipur       N002 
North   Delhi        N003
South   Chennai      S001
South   Hyderabad    S002
South   Chennai      S003
East    Kolkata      E001
East    Patna        E002
East    Patna        E003
West    Mumbai       W001
West    Pune         W002
West    Mumbai       W003

Regards,
Mukesh


#2

Hello Mukesh,

Attached is a solution for multilevel dropdown selections in Excel. The dropdowns are in cells B4, C4, and D4 based on your input table above.

I broke down the problem into several steps and used a few tricks up my sleeve so you may have to study the formulas carefully. I took advantage of a cool trick in Excel of being able to define named ranges dynamically. For example, in the Name Manager, see how I defined the range name “City.dropdown”, which defines itself according to the number of distinct eligible cities narrowed down according to what the user selects in the Region dropdown.

In the “Eligible Selections” in columns F-H, I used “zzz” to represent an item to be ranked last in columns J-L. Just make sure that none of your original inputs can possibly be sorted after “zzz” alphabetically.

Note that there are actually always an entry in the dropdowns for City and Outlet in cells C4 and D4. These entries may be incorrect according to what the user selects in the Region dropdown, so I conditionally formatted cells C4 and D4 to be font yellow and background yellow to indicate to the user that they should pick an appropriate City and Outlet.

Hope this helps!

P.S. It seems I cannot attach an Excel file in response to your post. If you tell me your email address I can email you the Excel file.


#3

@Mukesh

You can use named range and Indirect function to perform this. Below are steps to perform it.

Step-1: Group every city of a particular region in a adjacent range of cells and define the range with the name of respective region.

Like in your example, lets say Delhi, Jaipur in in cells Q4:Q5 and define this range as North and similar for city of regions South, East and West also.

Step-2: Create a data validation for Regions. Go to data validation and select list option and provide North, East, South, West as Source

Step-3: Link city with region value using indirect function in list option of data validation. In below snapshot, region drop down is available in cell N10.

Hope this helps!

Regards,
Sunil