2.5 Dependent Drop-down: City › Area
When the user picks a City, the Area drop-down should show only that city's areas.
| Pune | Solapur | Nashik | Sambhaji_Nagar | Kolhapur | Nagpur |
|---|---|---|---|---|---|
| Kothrud | Hotgi Road | College Road | CIDCO | Rajarampuri | Dharampeth |
| Hinjewadi | Murarji Peth | Gangapur Road | Nirala Bazar | Tarabai Park | Sitabuldi |
| Baner | |||||
| Hadapsar | |||||
| Wakad |
Method A – named ranges + INDIRECT (all versions)
Steps in Excel
- Type the table above on the
Listssheet (headers in row 1). Note Sambhaji_Nagar uses an underscore because a name cannot contain a space. - Select the whole block › Home › Editing › Find & Select › Go To Special › Constants (selects only filled cells).
- Formulas › Defined Names › Create from Selection › tick only Top row › OK. Excel creates names
Pune,Solapur,Nashik,Sambhaji_Nagar,Kolhapur,Nagpur. - City drop-down in
Orders!D2:D1000: List source=CityList(the vertical list of the six real city names from 2.4). -
Area drop-down in E2:E1000: List source:
=INDIRECT(SUBSTITUTE(D2," ","_")) -
Pick Sambhaji Nagar in D2 – the SUBSTITUTE turns it into
Sambhaji_Nagar, INDIRECT turns that text into the named range, and E2 shows CIDCO and Nirala Bazar.
Method B – FILTER (Microsoft 365 / Excel 2021+)
Keep a two-column mapping table CityArea (City, Area). In a helper cell, say Lists!J2:
=FILTER(CityArea[Area], CityArea[City]=Orders!D2)
Then Area validation source: =Lists!$J$2#. This works for one input row at a time (a dashboard selector). For many rows, Method A is simpler.
Ravindra Bagale's Tip
In a dependent drop-down, the common mistake is that the city name and the named range name are slightly different – "Sambhaji Nagar" vs "Sambhaji_Nagar", or a space at the end. Then INDIRECT gives #REF! and the list appears empty. Use SUBSTITUTE, and select the City first and then the Area – if you do it the other way round, the old area stays as it is.
Ravindra Bagale's Tip – मराठी
Dependent drop-down मध्ये बऱ्याच students ची चूक म्हणजे city चं नाव आणि named range चं नाव थोडंसं वेगळं असणं – "Sambhaji Nagar" vs "Sambhaji_Nagar", किंवा शेवटी space. मग INDIRECT #REF! देतो आणि list रिकामी दिसते. SUBSTITUTE वापरा, आणि आधी City select करा मग Area – उलटं केलं तर जुना area तसाच राहतो.
Ravindra Bagale's Tip – हिंदी
Dependent drop-down में बहुत से students की गलती होती है कि city का नाम और named range का नाम थोड़ा अलग होता है – "Sambhaji Nagar" vs "Sambhaji_Nagar", या आख़िर में space. फिर INDIRECT #REF! देता है और list खाली दिखती है. SUBSTITUTE इस्तेमाल करो, और पहले City select करो फिर Area – उल्टा किया तो पुराना area वैसा ही रह जाता है.
Practice task
Build the City › Area dependent drop-down for rows 2–100. Test all six cities. Bonus: add a conditional-formatting rule (2.8) that turns the Area cell red if it does not belong to the chosen city.