When new elements are inserted into the bottom of an Excel table, it extends dynamically. Tables are one of the most effective features in an Excel user’s toolbox simply because of this capability. A data Validation list is used to keep the Table data out of Error. But we need to update the Data Validation list while adding new data to the Table. In this tutorial, we will show you, how to make a dynamic Excel Data Validation list from the table.
3 Effective Ways to Make a Data Validation List from Table in Excel
In the image below, there is a sample data set to apply the validation list.
To do this, generally, we will open the Data Validation option from the Data tab.
Then, we will select the List option as Allow and type the table name with the header (Table179[States]).
But it won’t work. It will show this message box as shown in the image below. We will use three approaches to fix the problem. First, we’ll apply cell references, then a named range, and lastly, the INDIRECT function will be assigned to the Data Validation list.
1. Apply Cell References in Data Validation List from Table in Excel
For applying direct cell references in the Data Validation list, follow the outlined steps below.
Step 1:
- Go to the Data tab and select the Data Validation.
- Select the List in the Allow.
Step 2:
- In the Source box, select the range B5:B11 without the header in the Table.
- Finally, press Enter.
Step 3:
- Therefore, your Data Validation drop-down list will appear.
Step 4:
- Now, add type an extra element ‘Texas’ at the bottom of the table.
Step 5:
- As a result, the ‘Texas’ is added to the Data Validation
Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel
2. Use a Named Range in Data Validation List from Table in Excel
You can apply a name to the range in Table. To create a Data Validation list by naming the Table, follow the instructions below.
Step 1:
- Select the cells in the range without the Table Header.
Step 2:
- Then, click on the Formulas Tab.
- Click on the Name Manager.
Step 3:
- Then, click on the New.
Step 4:
- Type any name you want to introduce, we have typed ‘Named_Range’.
- Press Enter.
Step 5:
- In the Data Validation Source box, type the following name.
=Named_Range
Step 6:
- Finally, press Enter to see the list.
Step 7:
- In the bottom cell of the table, type ‘Texas’.
Step 8:
- Therefore, the ‘Texas’ option will be added to the drop-down option.
Read More: How to Use Named Range for Data Validation List with VBA in Excel
Similar Readings
- How to Apply Multiple Data Validation in One Cell in Excel (3 Examples)
- Excel Data Validation Drop Down List with Filter (2 Examples)
- Autocomplete Data Validation Drop Down List in Excel (2 Methods)
- Excel Data Validation Alphanumeric Only (Using Custom Formula)
- Excel Data Validation Based on Another Cell Value
3. Insert the INDIRECT Function in Data Validation List
Moreover, we can use functions in the Data Validation box. We will apply the INDIRECT function in the Data Validation Source box. The INDIRECT function is used to find the range of a certain text. It returns the range under a certain cell value. To apply the function, follow the steps below.
Step 1:
- In any cell, type ‘=’ equal to sign and select the range.
- Copy the range name ‘Table18[States]’.
Step 2:
- Then, type the following formula with the INDIRECT
=INDIRECT("Table18[States]")
Step 3:
- Finally, press Enter to see the list.
Step 4:
- Insert a text at the bottom of the table.
Step 5:
- Therefore, it will be added to the Data Validation list automatically.
Read More: Excel VBA to Create Data Validation List from Array
Conclusion
Finally, I hope you have a better understanding of how to create an Excel Data Validation List from a Table. All of these strategies should be implemented while educating and practicing with your data. Examine the practice book and apply what you’ve learned. We are inspired to continue delivering programs like this because of your valued support.
If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.
The Exceldemy staff will get back to you as soon as possible.
Stay with us and continue to learn.
Related Articles
- How to Use IF Statement in Data Validation Formula in Excel (6 Ways)
- How to Use Data Validation in Excel with Color (4 Ways)
- [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
- How to Use Data Validation List from Another Sheet (6 Methods)
- Use Custom VLOOKUP Formula in Excel Data Validation
- How to Remove Blanks from Data Validation List in Excel (5 Methods)