Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


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.

share|improve this question

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


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

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.

share|improve this answer

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.

share|improve this answer
Problem is, Years is only one of the columns. I also have a country, state, district, and type. The list of all possible values in these columns, unlike years, will be unknown until we get new the new set of data. – Daniel T. Sep 12 '11 at 0:17

Add a Pivot Table from the data on another sheet. Use Years as rows, the rest of the table is irrelevant. Sort and filter the rows as required (eg explicitly filter OUT "[blank]"). Make the cells where the row labels are into the target of your data validation.

Use an expanding named range to always use all the new labels:

Repeat for other columns such as country, state etc. If you base all the pivots off the same data range they will all refresh together when you refresh any one of them. Your process will now be: add new data, refresh pivots, use updated data validation.

Further thoughts: Using a table for the source data will make it easy to update the Pivot Tables since they will always use the whole table. Alternatively use an expanding named range as the data source to avoid issues when you add more rows.

Note that you can use a range from a different worksheet as a source for data validation if you do this by defining a named range. You can't use a normal range explicitly referring to another sheet.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .