Is there any technique which can be used in Excel to combine a list of default entries for a data validation drop-down list plus the values which have been added by the user where the data validation list is not constrained?

There is one means of achieving this which used a pivot table to create the range of values for the drop-down. The only drawback is that the pivot table needs to be refreshed before new values entered show up in the dropdown. This can be achieved most easily by including the Refresh all button in the Quick Launch menu bar.

In order to get bot the static and dynamic values to show up, we need to use a pivot table which consolidates two separate ranges. The first range will be a dynamic named range which looks up the entries that the user has entered in the column including any from the fixed list. Create a dynamic named range using the OFFSET formula as follows:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),2)

Note that the range must include two columns, but it is irrelevant what is in the adjacent column as it will not be used.

Next, create the list of fixed values, perhaps on another worksheet which can then be hidden from the user.

Make sure that you title the first column with the same name as used by the drop-down, so that the pivot table can consolidate both ranges.

To create the pivot table, use Alt D, P to launch the Pivot Table wizard and choose Multiple Consolidate ranges:

Add two ranges, the first will be the dynamic range created initially, the second must be a range covering the title and values in the fixed column, plus the column adjacent to the right, no matter what is in it.

The resulting pivot table will contain single entries from both ranges.

Create a dynamic range based on the pivot table, to pick up just the required values form the list:

=OFFSET(Sheet2!$H$5,0,0,COUNTA(Sheet2!$H:$H)-4,1)

Set the data validation on your column to this named range and it will combine entries from the fixed list plus any entries added by the user, once the pivot table has been refreshed.

Advertisements