Friday, January 22, 2016

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 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 .

No comments:

Post a Comment