A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you can disable the validation and still use the drop down list?
Entering a value that is not in the drop down list will not cause an error alert. My sheet allows you to type a new value and it is instantly included to the drop down list. The drop down list contains unique distinct values extracted from cells in the excel defined table.
The following picture shows what I am trying to explain.
The great thing about drop down lists and excel defined tables is that you don't need to copy the drop down list to new cells in the table. The excel defined table does that for you. There is no vba in this post. How is this done?
Insert an excel defined table
Insert an excel defined table or convert an existing range. You can use the shortcut keys CTRL + T or
Go to tab "Insert" on the ribbon
Click "Table" button.
Insert a new "helper" sheet
Insert a new sheet, it is going to be our "helper" sheet. I renamed it to "Sheet2".
The formula in cell A2 extracts unique distinct values from Table1[Name].