Ravindra BagaleCourses & study guides मराठी Track your progress

2. Data Entry Tools

2.9 Named Lists for Drop-downs

By Last updated:

Example first. Ravina keeps a delivery log where the City column must only take one of five cities. Instead of typing the cities into every Data Validation box, she keeps them on a Lists sheet, gives them a name, CityList, and points the drop-down at that name. When Sambhaji Nagar is added to the list, every drop-down shows it.

Named list for a drop-down — in 30 seconds

What: A named list is a range (or Table column) on a helper sheet that has a name, like CityList. A Data Validation drop-down can use that name as its Source.

Why: One list feeds every drop-down in the workbook. Fix a spelling once and every drop-down is fixed. With a Table behind the name, the list grows by itself.

Tiny example: Source =CityList shows Pune, Nashik, Nagpur, Kolhapur, Solapur. Add Sambhaji Nagar under the Table on the Lists sheet and the drop-down shows six cities.

First look at the data (before → after):

Named list for a drop-down: before and after tables, fictional Blinkit Maharashtra data

Expected result — Drop-down in Log!C2 (Source =CityList)

Item in the drop-down
Pune
Nashik
Nagpur
Kolhapur
Solapur
Sambhaji Nagar

Expected result — What happens when the list grows

Setup Cities in the drop-down after adding Sambhaji Nagar
Source typed as Pune,Nashik,… 5 (must edit every rule)
Name on a fixed range =Lists!$A$2:$A$6 5 (name must be edited)
Name on a Table column =tblCities[City] 6 (grows by itself)
Before table and formula as text

Before — Sheet Lists: a Table tblCities with five cities

A: City
Pune
Nashik
Nagpur
Kolhapur
Solapur

Formula / click path

Formulas › Name Manager › New › Name: CityList · Refers to: =tblCities[City]
Data › Data Validation › Allow: List › Source: =CityList

Do it step by step:

  1. On a sheet named Lists, type City in A1 and the five cities in A2:A6.
  2. Click inside the list, press Ctrl + T and name the Table tblCities.
  3. Click Formulas › Name Manager › New. Type CityList in Name and =tblCities[City] in Refers to. Click OK, then Close.
  4. Go to the Log sheet and select C2:C100.
  5. Click Data › Data Validation, choose Allow: List and type =CityList in Source. Click OK.
  6. Type Sambhaji Nagar in Lists!A7 and open the drop-down in C2 again. It shows six cities.

Why not type =tblCities[City] straight into Data Validation? The Source box does not accept a structured reference. Give the column a name first (as above) and use the name. This is the cleanest way to get a list that grows.

Three ways to feed a drop-down

Source Grows by itself? Use when
Typed list: Pune,Nashik,Nagpur No Two or three fixed choices like Yes,No
Name on a fixed range: =Lists!$A$2:$A$6 No (edit the name in Name Manager) The list almost never changes
Name on a Table column: =tblCities[City] Yes Most real work: cities, stores, products, staff

Name Manager in one minute. Formulas › Name Manager lists every name with what it refers to. Use Edit to fix a name and Delete to remove one you no longer use. A name that shows #REF! points at deleted cells; fix it before you share the file.

Related lessons: 1.8 Named Ranges explains names in formulas, and 2.4 Drop-down Lists explains the basic drop-down.

Practice task (try it before you open the answer):

  1. Build the Lists sheet and the tblCities Table.
  2. Create the name CityList in Name Manager.
  3. Add a drop-down in Log!C2:C20 with Source =CityList.
  4. Add Sambhaji Nagar and then Kolhapur again by mistake. What does the drop-down show?
Answer

After Sambhaji Nagar the drop-down shows 6 cities. After the duplicate Kolhapur it shows 7 items, with Kolhapur twice: a drop-down shows the list exactly as it is. Remove duplicates in the list with Data › Remove Duplicates.

Ravindra Bagale's Tip

Keep all your lists on one sheet named Lists, one Table per list, and hide that sheet only after everything works. Hidden lists are the first place a new colleague forgets to look.

Thodkyaat sangaycha tar (quick recap)

AutoFill aani Series ne pattern bhara, Flash Fill one-time split/combine sathi, data validation aani drop-downs ne chukiche values thamba, dependent drop-down sathi INDIRECT kiwa FILTER, aani conditional formatting ne mahatvache aakde lagech dakhva – formula rules madhe $ lakshat theva. Aata pudhe jaauya – formulas chya jagat!

Loading practice tasks...

  1. VLOOKUP name (Easy): Name for ID "C103" with VLOOKUP.
  2. XLOOKUP value (Easy): The Price per seat for ID "C103".
  3. Position (Easy): Position of "Aarav Shah" in B2:B9.
  4. INDEX and MATCH (Medium): ID for name "Aarav Shah" using INDEX and MATCH.
  5. Not found message (Medium): Seats for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP (Medium): VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH (Medium): Position of ID "C103" in A2:A9.
  8. Slab rate (Hard): Rate for E2 from the slab table G2:H6.
  9. Slab amount (Hard): E3 times its slab rate.
  10. Two-way lookup (Hard): Value in A2:E9 at the row of "Aarav Shah" and column "Seats".
  11. Lookup the top (Hard): Name with the largest Seats.

See all practice tasks in the Task Hub →