Search code examples
exceluniquedropdown

Excel Data validation list with array formula

I have a table with duplicated values in a column. How can I use this column, to provide unique values as a dropdown options in another cell? I want to be able to enter new rows in my table that might include already existing or new values and dropdown should automatically reflect this.

What I tried with UNIQUE(MyTable[MyColumn]):

  • Excel is not accepting this formula as a data validation source
  • I can spill UNIQUE(MyTable[MyColumn]) to range and name this range and use it as a data validation source, but the named range will not expand/contract automatically when my table data change
  • Excel will not accept UNIQUE(MyTable[MyColumn]) in a new table
Solution

  • You are on the right way - and yes: this is annoying and definitly not intuitiv.

    You have to add a # after the reference to your range when naming it:

    enter image description here

    Then use the name for your validation list. It will now expand when you add a new row to the table.

    D3: UNIQUE-Formula referencing the table-column Name "lstValues": referencing $D$3# then use lstValues