Drop down list of unique values - without helper column

[Villalobos]

Hello,

I would like to ask that can be create a drop down list (for the unique values) without helper column, the data are stored in dynamic range as a "Table" ($A$2:A) ?

For instance:

DATE
17.06.2014
19.06.2014
17.06.2014
18.06.2014
19.06.2014
19.06.2014
20.06.2014

The drop down list would be in B2.

Thanks in advance the reply!

 

[Luke M]

You would not be able to create a data validation drop down as you describe w/o using helper cells. The Data Validation, to create a list, needs a range of cells somewhere.

 

[p45cal]

Would the attached do?
It's a cheat, it uses a 2 cell pivot table but it's mighty qiuck to do.
It would need refreshing when the table changes but that can be automated.

ps. If it's a table, doesn't the header in column A already contain a dropdown?

 

[Villalobos]

Hi Luke M,

And is there a VBA solution?
I have a code what use on userform, is it possible to change somehow to be used in cell B2?

 

[p45cal]

Sub blah()
Dim g As Long, h As Long
Set colListA = New Collection
With Worksheets("Data")
  For g = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
  On Error Resume Next
  colListA.Add .Cells(g, 1).Value, CStr(.Cells(g, 1))
  Next g
  On Error GoTo 0
  For h = 1 To colListA.Count
  myformula = myformula & "," & colListA(h)
  Next h
  myformula = Mid(myformula, 2)
End With
With Range("B2").Validation
  .Delete
  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=myformula
  .IgnoreBlank = True
  .InCellDropdown = True
  .InputTitle = ""
  .ErrorTitle = ""
  .InputMessage = ""
  .ErrorMessage = ""
  .ShowInput = True
  .ShowError = True
End With
End Sub

 

[Villalobos]

Thank you p45cal!