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 page 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 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 page and to desired cells .
