Others

How to Load States in a Dependent Country Drop-Down List

Maira MehtabMaira Mehtab Oct 12, 2026 871 views

Question details

The user needs to populate a secondary drop-down list with states or provinces dynamically based on the country selected in the first drop-down, potentially using a Google API or external data service.

How to Load States or Provinces in a Dependent Country Drop-Down
Product
Spreadsheets / Microsoft 365
Device & OS
not provided
Scenario
Creating a dynamic data entry form or spreadsheet where selecting a country automatically updates the available options in the state/province field.
Observed behavior
Requires a method to bind state or province data to a secondary drop-down after a country selection is made, parsing data accurately.
Before you start

Before configuring your dependent drop-downs, ensure that your raw data for countries and states is organized logically without blank cells, and verify your API endpoint configuration if you plan to fetch administrative regions externally.

Solution 1Recommended

Create Dependent Drop-Downs Using Named Ranges and INDIRECT

The most reliable and accessible way to create a dependent drop-down list in spreadsheets without writing code is by utilizing Named Ranges and the INDIRECT function.

This native spreadsheet method avoids complex scripting and external API calls. It relies on grouping your state or province data under specific names that match your country list exactly.

1
Create your primary and secondary data lists

In a new worksheet, type your list of countries in one column. Then, in separate columns, type the corresponding states or provinces for each country. Use the country name as the header for each state column.

2
Define Named Ranges for your states

Highlight a column of states (excluding the header), go to the Formulas tab, and click 'Define Name'. Name this range exactly as the country header. Repeat this for all country columns.

3
Set up the primary country drop-down

Select the cell where you want the country drop-down to appear. Go to Data > Data Validation. Under the 'Allow' drop-down, choose 'List', and highlight your main list of countries for the 'Source' field.

4
Configure the dependent state drop-down

Select the cell for your state drop-down. Go to Data > Data Validation and choose 'List'. In the 'Source' field, type =INDIRECT(A2) (assuming A2 is the cell containing your country drop-down) and click OK.

Create Dependent Drop-Downs Using Named Ranges and INDIRECT
Formatting Tip: If your country names contain spaces (e.g., 'United States'), your Named Range cannot have a space. Name the range 'United_States' and use the formula =INDIRECT(SUBSTITUTE(A2," ","_")) in your data validation source.
Efficient Spreadsheet Data Management

Create Dynamic Drop-Down Lists Easily in WPS Spreadsheet

WPS Spreadsheet provides robust, easy-to-use data validation features, allowing you to create dependent drop-down lists for countries and states seamlessly. It fully supports named ranges and INDIRECT formulas, letting you organize complex data entry forms without relying on external APIs or writing code.

  1. 1. Organize your data: Open your document in WPS Spreadsheet. List your primary categories (countries) and organize your subcategories (states) under respective headers.
  2. 2. Create Named Ranges instantly: Highlight your data blocks, navigate to the Formulas tab, and use the 'Create from Selection' tool to automatically name your state ranges based on the top row headers.
  3. 3. Apply Data Validation: Select your target cell, click on 'Data Validation' under the Data tab, and choose 'List' from the criteria options.
  4. 4. Link the drop-downs: Input your =INDIRECT() formula pointing to the primary country cell to dynamically link the two drop-down menus.
Fully compatible with Microsoft Excel (.xlsx) formats and formulasIntuitive Data Validation tool for building dependent lists quicklySeamless support for advanced functions like INDIRECT and SUBSTITUTEFree, lightweight, and fast alternative for all your spreadsheet needs
microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT formula returning an error in the data validation box?

This commonly occurs if the primary drop-down cell is currently empty, or if the selected country name contains spaces while your Named Range does not. Ensure you select a country first, and use the formula =INDIRECT(SUBSTITUTE(A1," ","_")) to handle country names with spaces correctly.

Can I link more than two dependent drop-down lists?

Yes, you can create a three-level dependent list (e.g., Country > State > City). Simply create named ranges for the cities using the state names as headers, and use another INDIRECT formula in the city cell that references the selected state cell.

What if I must use an external API for my drop-down data?

Using an external API requires custom scripting because standard spreadsheet formulas cannot make direct HTTP requests. You will need to use VBA in Excel, Google Apps Script in Sheets, or Office Scripts to fetch the JSON data, parse it, and dynamically update the validation rule of your target cell.

Does the INDIRECT method work on mobile spreadsheet apps?

Yes, once the data validation and INDIRECT formulas are properly set up on a desktop version like WPS Spreadsheet, the dependent drop-down lists will continue to function correctly when users open the file on mobile spreadsheet applications.