How to Use an Excel Table in Data Validation Drop-Down Lists in WPS Office
Question details
The user wants to use an Excel Table as the source for a Data Validation drop-down list in WPS Office and needs a reliable dynamic reference because structured table references are not always accepted directly.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating a spreadsheet drop-down list that updates automatically when new rows are added to an Excel Table.
- Observed behavior
- Excel Data Validation may not accept a structured table reference directly as the list source, so the dropdown does not remain dynamic unless a named range or INDIRECT reference is used.
Confirm that your list source is formatted as an Excel Table and note the table and column names you will reference. Open the worksheet where you want the Data Validation drop-down list to appear.
Create a Named Range from a Table Column and Use It in Data Validation
Use this method to create a stable, dynamic dropdown source that expands automatically as the Excel Table grows.
A named range gives the Data Validation rule a simple, readable source such as =location_table. Because the named range refers to the table column, new table rows are included automatically.
Click the Formulas tab in the ribbon, then click Name Manager. In the Name Manager dialog, click New.
In the New Name dialog, enter location_table in the Name box. Click in the Refers to box, select the table column cells, and confirm the reference appears as =TableName[ColumnName]. Click OK and then Close.
Select the cell where the dropdown should appear. Click the Data tab, then click Data Validation > Data Validation. In the Allow box, choose List, and in the Source box enter =location_table. Click OK.
Use the INDIRECT Function to Reference a Named Table in Data Validation
Use this formula-based approach when you prefer a dynamic validation source through an INDIRECT reference instead of typing a named range directly into Source.
Use a Fixed Range Reference for a Static Dropdown List
Use this only for a fixed list that will not expand automatically; a normal range reference is simple but stays static.
Create Dynamic Excel Table Drop-Down Lists in WPS Office Spreadsheets
Use WPS Office Spreadsheets to create a Data Validation dropdown from an Excel Table with named ranges or INDIRECT references, so your list expands automatically while staying compatible with .xlsx workbooks.
- 1. Open your workbook: Launch WPS Office Spreadsheets, open the workbook that contains the Excel Table, and select the sheet where the dropdown is needed.
- 2. Create a named range: Click the Formulas tab and open Name Manager. Select New, enter a name such as location_table, and set the reference to the table column.
- 3. Insert the Data Validation dropdown: Select the dropdown cell, click Data > Data Validation, choose List in the Allow box, and enter =location_table in Source.
- 4. Test the dynamic list: Click the dropdown arrow to check the list. Add a new row to the table and confirm the dropdown includes the new item automatically.
Frequently Asked Questions
Why does Excel Data Validation reject a table reference directly?
Data Validation list sources expect a range reference or a formula. Use a named range that refers to the table column, or use INDIRECT with a defined name, so the structured reference is accepted and remains dynamic.
How do I make a dropdown list update automatically when I add rows to an Excel Table?
Create a named range such as location_table that refers to the table column. Then open Data > Data Validation, choose List, and enter =location_table in the Source box.
Can I use INDIRECT with a table name in Data Validation?
Yes. First create a defined name such as location that refers to the table column. Then enter =INDIRECT("location") as the Data Validation Source.
Does a fixed range reference like =$A$8:$A$9 expand automatically?
No. A fixed range stays static. To get an automatic update when rows are added, reference the Excel Table column through a named range or an INDIRECT formula.
Is this Data Validation method available in WPS Office?
Yes. WPS Office Spreadsheets includes the Data Validation dialog and Name Manager, so you can use named ranges or INDIRECT references in .xlsx files with a familiar ribbon workflow.
