How to Load States in a Dependent Country Drop-Down List
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.

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

Populate Drop-Downs Dynamically Using an API Service
For large, constantly updating datasets, use a development script in your Microsoft 365 environment to fetch states or provinces from a Google API or data service.
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. Organize your data: Open your document in WPS Spreadsheet. List your primary categories (countries) and organize your subcategories (states) under respective headers.
- 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. Apply Data Validation: Select your target cell, click on 'Data Validation' under the Data tab, and choose 'List' from the criteria options.
- 4. Link the drop-downs: Input your =INDIRECT() formula pointing to the primary country cell to dynamically link the two drop-down menus.

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.




