Create a Dynamic Data Validation List

When you create a Data Validation list in Excel it is locked to the range you select when you first create the list. There are couple of ways of making this list dynamic allowing your list to adapt whenever you add or delete data from the source list. One way is to use a formula with the OFFSET function. In this article we will show you how to create a dynamic Data Validation list without the need for a formula.
There is a link at the bottom of this article to download the sample workbook if you wish to follow along in this example.

Here we have two worksheets: one named Source and the other named Data.

The image below shows the Source worksheet containing the list of names we will use as the source for the Data Validation list.
Dynamic Data Validation2

The image below shows the Data worksheet where we will add the Data Validation list to cell A2.
Dynamic Data Validation1

The first step is to convert range containing the list of names in to a Table.

Select the Source worksheet.
Click on any name in the list of names.
Dynamic Data Validation3

Click the Insert tab and select Table from the Tables group.
You can use the keyboard shortcut Ctrl + T.
Dynamic Data Validation4

You will now see the dancing ants around the range containing the list of names. You will also see the Create Table options box showing the range address we are using and the option My table has headers. The list we are using has a header therefore we will check this box.
Dynamic Data Validation3

Click OK and you will see the range has now been converted to a table.
Dynamic Data Validation6

We will now name the range containing the list of names required for the Data Validation list.
Select all the names on the list but NOT the header.
Type the word Names into the Name Box.
Press Enter.
Dynamic Data Validation7

Click the Data tab.
Select Cell A2
Select Data Validation from the Data Tools group.
Dynamic Data Validation9

Click the dropdown menu under Allow:.
Select List from the options.
Dynamic Data Validation10

Click in the Source box.
Press F3 on your keyboard.
A list of all named ranges will be displayed and you will see the range Names that we created earlier.
Select Names and click OK.
Dynamic Data Validation11

The Data Validation options box should now look like this.
Click OK
Dynamic Data Validation12

You will now have a dropdown list in cell A2 of the Data worksheet containing a list of names from the Source worksheet.

Click on the list and scroll to the bottom.
The last name on the list will be Kibo.
Dynamic Data Validation13

Select the Source worksheet.
Add the name Peter to the bottom of the list.
Dynamic Data Validation14

Select the Data worksheet.
Click on the dropdown list.
Notice that Peter has now been added to the bottom of the list.
Dynamic Data Validation15

You now have a dynamic Data Validation list.




2 Comments

  1. you’re truly a just right webmaster. The site loading speed is incredible. It kind of feels that you are doing any distinctive trick. In addition, The contents are masterpiece. you’ve performed a magnificent task in this subject! ekedefcbbead

Leave a Reply