Excel Data Validation List With Unique Values

Create Data Validation List Based On Unique Entries 

             It is necessary to fill it with unique values to use the data validation list easily.    

        

            For this reason ,we created a unique values of columns  with the VBA function to another sheet and we sorted values in ascending order.Later ,we defined names for each column that  of the unique values :

✓ Defined Names : Col1 ,Col2, Col3, Col4, Col5, Col6, Col7, Col8

Codes Of The Defining Names :

Sub define_names()
ActiveWorkbook.Names.Add Name:="Col1", RefersTo:="=OFFSET(Unique_Values!$A$2,0,0,COUNTA(Unique_Values!$A:$A)-1)"
ActiveWorkbook.Names.Add Name:="Col2", RefersTo:="=OFFSET(Unique_Values!$B$2,0,0,COUNTA(Unique_Values!$B:$B)-1)"
ActiveWorkbook.Names.Add Name:="Col3", RefersTo:="=OFFSET(Unique_Values!$C$2,0,0,COUNTA(Unique_Values!$C:$C)-1)"
ActiveWorkbook.Names.Add Name:="Col4", RefersTo:="=OFFSET(Unique_Values!$D$2,0,0,COUNTA(Unique_Values!$D:$D)-1)"
ActiveWorkbook.Names.Add Name:="Col5", RefersTo:="=OFFSET(Unique_Values!$E$2,0,0,COUNTA(Unique_Values!$E:$E)-1)"
ActiveWorkbook.Names.Add Name:="Col6", RefersTo:="=OFFSET(Unique_Values!$F$2,0,0,COUNTA(Unique_Values!$F:$F)-1)"
ActiveWorkbook.Names.Add Name:="Col7", RefersTo:="=OFFSET(Unique_Values!$G$2,0,0,COUNTA(Unique_Values!$G:$G)-1)"
ActiveWorkbook.Names.Add Name:="Col8", RefersTo:="=OFFSET(Unique_Values!$H$2,0,0,COUNTA(Unique_Values!$H:$H)-1)"
End Sub

If to understand  the cause of the OFFSET formula used in defining names ; for example :

=OFFSET(Unique_Values!$A$2,0,0,COUNTA(Unique_Values!$A:$A)-1)

Unique_Values!$A$2: Start at cell $A$2, which is the first value in the list
0: Stay in that same row (so still at $A$2)
0: Stay in that same column (so, again, still at $A$2)
COUNTA(Unique_Values!$A:$A)-1: count the number of cells in column A that have values and then subtract 1 (the heading cell: “Priority”); grab an area that is that tall, starting with the cell currently “selected” ($A$2)

Data validation list can be added to the requested sheet and to desired cells .