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:
- It displays the duplicate years.
- 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.