Ravindra BagaleCourses & study guides

2. Data Entry Tools

2.5 Dependent Drop-down: City › Area

By Last updated:

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

  1. Type the table above on the Lists sheet (headers in row 1). Note Sambhaji_Nagar uses an underscore because a name cannot contain a space.
  2. Select the whole block › Home › Editing › Find & Select › Go To Special › Constants (selects only filled cells).
  3. Formulas › Defined Names › Create from Selection › tick only Top row › OK. Excel creates names Pune, Solapur, Nashik, Sambhaji_Nagar, Kolhapur, Nagpur.
  4. City drop-down in Orders!D2:D1000: List source =CityList (the vertical list of the six real city names from 2.4).

  5. Area drop-down in E2:E1000: List source:

    =INDIRECT(SUBSTITUTE(D2," ","_"))

  6. 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

Dependent drop-down madhe khup students chi chuk mhanje city che nav aani named range che nav thodasa vegla asne – "Sambhaji Nagar" vs "Sambhaji_Nagar", kiwa shevti space. Mag INDIRECT #REF! deto aani list rikami disate. SUBSTITUTE vapra, aani City select karun mag Area select kara – ulta kela tar juna area tasach rahto.

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.