Excel Table Appears to Automatically Expand but drop down list doesn't update

There is a much cleaner implementation that always uses the full set of rows in a column of a table and avoids the need to define named ranges which become a hassle to keep synchronized with table contents.  Use an INDIRECT() formula in the Source field.

Source:

=INDIRECT("TableName[ColumnName]")

For example.  Create a table named TYesOrNo.

Name the first column with a standard name, such as ID.

Make this column the first column in your table and you now have the basis for easy VLOOKUP() formulas to pull other parameters from the table columns based upon the field that is constrained to have a value that exists in that table.

You can also make the column itself a formula that produces a unique value from one or more other values in the table row.  In this case, the ID field is a calculated field using just the Code, =[Code].  It could just as easily be changed to =[Code] & " - " & [Description], such that the dropdown values would contain Yes - Definitely Yes and No - Definitely No, or any other calculation that produces a useful and unique value for every row.

If you add a Sort column, you can easily sort the lookup table when you make changes to make the dropdown picklist occur in the order you want regardless of the alphabetical sort.

Define the Data Validation as an INDIRECT() reference to the table and unique identity column:

Here's what the dropdown list looks like when applied to a column of another table:

If you use a standard ColumnName (for example, ID or Code) for the unique value in all of your lookup tables then the formula only needs to have the TableName modified.  Copying and pasting the Data Validation formula to re-use it for other fields is simpler with only one name to edit.  Editing this field is a pain because the arrow keys produce formula references instead of moving the insertion point. 

It took a long time for me to find this solution.  I hope you find it useful.