Data Validation Drop Down List Multiple Selection Without Duplicates. The file is in excel 2007 format and is zipped. In the data validation dialogue box within the settings tab select list as validation criteria.
To allow multiple selections in a drop down list in excel without repetition private sub worksheet change byval target as range dim oldvalue as string dim newvalue as string application enableevents true on error goto exitsub if target address d 2 then if target specialcells xlcelltypeallvalidation is nothing then goto exitsub. All that remains is to set up the drop down box with the data validation feature data data validation. We simply allow a list equal to the name dd reps as shown below.
To allow multiple selections in a drop down list in excel without repetition private sub worksheet change byval target as range dim oldvalue as string dim newvalue as string application enableevents true on error goto exitsub if target address d 2 then if target specialcells xlcelltypeallvalidation is nothing then goto exitsub.
When you open the file enable macros if you want to test the block duplicates feature. Once the workbook is set up as new data is added to the table each period you just need to refresh the pivottable and any new reps will flow through the pivottable into the drop down. In the data validation dialog select list in allow list under settings tab select the pivottable list as the source data. Go to data data tools data validation.
