Data Validation Drop Down List with Excel Table Dynamic Range

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to create a data validation drop down list with an Excel table dynamic range. This will save you a lot of time and energy. The following picture highlights the purpose of this article. Have a quick look through to see how to do that.

Data Validation Drop Down List with Excel Table Dynamic Range


We will use the following Excel Table to highlight the methods. The table contains a list of names of some of the 13 original states of the USA. So let’s begin.


1. Using Defined Range for Data Validation Drop Down List with Excel Table Dynamic Range

Imagine you want to create the dynamic dropdown list in cell B4 in the DefinedRange worksheet. Then follow the steps below to be able to do that by creating a defined range.

📌 Steps

  • First, you need to select Formulas >> Name Manager to open the Name Manager window. You can use the CTRL+F3 shortcut to do that too.

  • Then select New on the Name Manager window.

  • Next, change the name to ListOfStates in the New Name dialog box. After that, enter the following formula in the Refers to: field. Then hit the OK button.
=Table1[States]
  • Here Table1 is the name of the dataset table. And States refers to the header of the table.

  • Now select cell B4 and then select Data >> Data Validation as shown in the following picture.

  • Next, go to the Settings tab in the Data Validation After that, choose List as the Validation criteria using the dropdown arrow.
  • Then, enter the following formula in the Source field and hit the OK You can also click on the Source field and then press F3 to do that.
=ListOfStates

  • After that, you will see the dropdown list working as shown below.

  • Now enter values in the adjacent cells below the table. After that, you will see the dropdown list to change accordingly.

Data Validation Drop Down List with Excel Table Dynamic Range Using Defined Range


2. Inserting INDIRECT Function to Make Data Validation Drop Down List with Excel Table

You can also create a dynamic dropdown list with the INDIRECT function. Applying the following steps will be sufficient for that.

📌 Steps

  • First, select cell B4 in the INDIRECT worksheet. Then select Data >> Data Validation as in the earlier method.
  • Next, set the Validation criteria to List. After that, enter the following formula in the Source field and then hit the OK button.
=INDIRECT("Table1[States]")
  • Here Table1 is the name of the dataset table. And States refers to the header of the table.

  • Then you will see the dropdown list working as follows.

  • Now, enter values in the adjacent cells below the table to verify if the dropdown list is dynamic or not. Then you will see the dropdown list changing accordingly.

Data Validation Drop Down List with Excel Table Dynamic Range Using INDIRECT Function


3. Applying OFFSET Function for Data Validation Drop Down List with Excel Table Dynamic Range

You can also use the OFFSET function to create a dynamic dropdown list with an Excel table. Follow the steps below to do that.

📌 Steps

  • First, select cell B4 in the worksheet named OFFSET. Then select Data >> Data Validation as in the earlier methods.
  • Now, choose List as Validation criteria from the Data Validation window.
  • After that, enter the following formula in the Source field and then hit the OK button.
=OFFSET(Dataset!$B$5,0,0,COUNTA(Dataset!$B:$B)-2,1)
  • The COUNTA function in the formula counts cells in a range excluding blank cells. -2 in the formula is used for the 2 cells (B2, B4) in the dataset which do not contain the actual data we want in the dropdown list.

Data Validation Drop Down List with Excel Table Dynamic Range Using OFFSET Function

  • Then you will see the dropdown list working as follows like in the earlier methods.

  • Now enter new values again in the adjacent cells below the table. After that, the dropdown list will be updated automatically.

Data Validation Drop Down List with Excel Table Dynamic Range Using OFFSET Function

Read More: OFFSET Function to Create & Use Dynamic Range in Excel


Things to Remember

  • The methods do not work properly if any cells in the table are blank. You can delete the rows containing the blank cells instead.
  • Do not change the table name after applying the second method.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to create a data validation dropdown list with an Excel table dynamic range. Please let us know if this article has solved your problem. You can use the comment section below for further queries or suggestions.

Stay with us and keep learning.


Related Articles


<< Go Back to Dynamic Range | Named Range | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo