2

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
Improve this question

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

For some reason Excel is not happy with # at he end of the range reference. It will accept named range but it won't allow to use it anywhere
The cell (!) you are referencing for the name contains the UNIQUE-Formula? Regarding the screenshot: see my update on answer
Ok, I know where my error was. I was entering # after the entire spill (D3:D8# in your screen). It should only be first value - D3#. Now Excel accepts =IstValues as a Data Validation source. I will be posting alternative answer that I came up in the meantime, but this works. Thanks!
1

Alternative solution

  • Insert Power Query referencing original table as a source: = Excel.CurrentWorkbook(){[Name="MyTable"]}[Content]
  • RMB on a column to keep, Remove Other Columns (if any)
  • RMB on a column, Remove Duplicates
  • Close and Load to the sheet as a table, name table DropdownTable
  • Define new named range DropdownValues with reference to =DropdownTable (or =DropdownTable[#All] to include header)
  • Use =DropdownValues as a data validation source
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.