Remove duplicates in drop list

[Toodz]

Hi guys,

I would like to know how to remove duplicates from a drop down list. I would point out that:

     - the source is on another sheet and is growing monthly

     - I don't want to create an intermediate list

Thanks in advance :)

[#2]

@Toodz 

The source of a data validation drop down list must either be a fixed list of values in the form of a text string (limited to 255 characters), or a range. It cannot be an expression that doesn't evaluate to a range. So I don't think you can do this without an intermediate list.

[#3 · Accepted solution]

@Toodz 

The best way to do this at present is with the intermediate list using:

=SORT(UNIQUE(range))

Then refer to the spill using # notation in the data validation menu.  Unfortunately, data validation won't accept UNIQUE so we must make do.  It's interesting that TAKE and DROP can be used in data validation but I don't believe there's a way to use them and only pull unique values if UNIQUE is off the table.

[#4]

Thanks @Patrick2788 ! You're the boss !