Drop-down list with new items added

Suppose we have a directory with the names of employees and a table where these employees need to be entered:

The task has two parts:

• Make a drop-down list, and so that when adding new people to the directory, they automatically appear in the drop-down list.
• Implement the ability to add new people to the list and, on the other hand, when you enter a new name in any of the yellow cells, it should be automatically added to the directory (and to the drop-down list in the future, by itself).
  

Such is the two-way connection of the directory and the drop-down list.

Step 1. Create a smart table

First, let’s turn the directory into a “smart” table in order to take advantage of one of its main advantages – dynamic auto-sizing when adding new data.

To do this, select the entire directory (cells A1: A7) and press the keyboard shortcut Ctrl+T or choose Home – Format as a table (Home — Format as Table). In the next window, you can safely press OK:

Step 2: Create a Dynamic Named Range

Now let’s create a named range that points to the cells filled with names in our reference book. To do this, select in the directory only the names without a header (cells A2: A7) and on the left side of the formula bar (there will be a table name) enter a name for our range (for example People):

After entering the name, be sure to press the key Enter – word People will disappear from this field, but the range will be created.

The trick here is that since we selected the column already in the “smart” table, then the named range turned out to be tied to the column [Directory], and not on specific selected cells. You can verify this by selecting on the tab formula Command Name Manager (Formulas — Name Manager) and see where the name refers People:

Thus, when adding new names to the directory, our “smart” Table 1, followed by our named range People.

Step 3. Create a drop-down list in the cell

Select the yellow cells and click on the tab Data (Date) button Data validation (Data Validation) 

Next, choose from the drop-down list Data type (Allow) position List (List) and enter in the line Source (Source) a reference to the named range created in step 1 (Don’t forget to put an equals sign in front of the range name!):

So that Excel will allow us to enter new names in the list in the future, uncheck the tabs Input message (Input Message) и Error message (Error Alert) and press OK. The dropdown list is ready!

Moreover, if, for example, you manually add a new name to the directory in column A, then it will automatically appear in the drop-down list in any of the yellow cells, since the names are taken from the dynamic range People:

Step 4. Add a simple macro

Now let’s insert a simple macro into our book that will track the input in the yellow cells and, when entering strangers, add them to the directory.

Right-click on our sheet tab and select View Code (View Source). The sheet module will open in the Visual Basic editor, where you need to copy the following code:

Private Sub Worksheet_Change(ByVal Target As Range)      Set p = Range("Люди")      If Target.Cells.Count > 1 Then Exit Sub      If IsEmpty(Target) Then Exit Sub      If Not Intersect(Target, Range("D2:D10")) Is Nothing Then          If WorksheetFunction.CountIf(p, Target) = 0 Then              r = MsgBox("Добавить новое имя в справочник?", vbYesNo)              If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target          End If      End If  End Sub  

Now, when you try to enter a new name in any of the yellow cells, Excel will ask:

… and if the user answers yes, automatically add a new name to the directory and to the drop-down list in the future.

• 4 Ways to Create a Dropdown List in a Sheet Cell
• How to create a list from which used items will be automatically removed
• Automatic list creation with the PLEX add-on
• Related drop-down lists (the content of the second depends on what is selected in the first one)
• Drop-down list for selecting images (photos of products, employees, etc.)