How do I create a dropdown list for a column of constantly-changing values in Excel 2010?

Question

I have an excel spreadsheet with a column for years, with a header:

Years
1993
1993
1994
1994
1994
...
2011
2011

There are duplicate values for the years, and additional rows will be added over time.

I have another cell that needs to show a dropdown list for the years, but only the unique years. I've tried using the data validation feature in Excel 2011, but it has 2 issues:

1. It displays the duplicate years.
2. I tell it to use the entire column, and it includes the empty cells in the dropdown list.

How do I get a dropdown list of years that will display only unique values, while automatically updating as additional rows are added?

Edit: a little more information. The dropdown list is used in a separate sheet to display calculated data, like an Access form. The user can pick a year range and the data will update accordingly. The original sheet is just a list of all the data.

Answer 1

For this kind of validations, I use VBA + one dirty trick:

First, enter VBA editor with Alt+F11 Then, I put my "Dynamic List Validation Code" (tm) :) in the respective worksheet.

Private Sub Worksheet_SelectionChange(ByVal rTarget As Excel.Range)

On Error GoTo noVal

With rTarget.Validation
    .Modify xlValidateList, xlValidAlertStop, xlBetween, Excel.Evaluate(.ErrorTitle)
End With

noVal:

End Sub

This code updates the cell validation list with the list generated by the formula entered in Data->Validation->Error Message->Title. This way, each cell with list validation can have its own formula.

Then, I add a module (Insert->Module) and then put this code in the new module:

Function GenDynList(rRng As Range)

sRet = ""

For Each rCell In rRng
    If Not IsEmpty(rCell.Value) And InStr(sRet, rCell.Value) = 0 Then
        sRet = sRet & "," & rCell.Value
    End If
Next

GenDynList = Mid(sRet, 2)

End Function

This function returns all the cells in the range without blanks or repetitions. Then, in each cell with list validation, I add GenDynList(range) in the Error Message title of data validation.

Answer 2

Messy. There's no built-in way to do that that automatically updates. It would be simpler to just create a separate list with all the possible years that you could be interested in rather than trying to limit it to those in your data set.