Creating a dynamic drop down list

[azizrasul]

I have a sheet that contains duplicated values in column A from which I want to create a dynamic drop down list without the duplicate values appearing. Is this possible?

 

[C Moore]

If you have version 365 you could use a combination of UNIQUE in an out of the way place and defined names where uniquelist in name manager =OFFSET(Sheet17!$K$3,0,0,COUNTA(Sheet17!$K:$K)). Wherever you use unique it would have to have nothing else in the column or the counta would return the wrong result.

scratchbook excel testing.xlsm
	J	K	L	M
3	1	1
4	2	2
5	3	3
6	4	4
7	5	5
8	6	6
9	7	7
10	8	8
11	9	9
12	10	10
13	6
14	7
15	8
16	9
17	10
Sheet17

Cell Formulas
Range	Formula
K3:K12	K3	=UNIQUE($J$3:$J$17)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
M3	List	=uniquelist

 

[azizrasul]

How would I change the formula if the value of 17 may vary as the user adds\deletes data in column J?

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you are using 365 then you could use

=UNIQUE(FILTER(J2:J1000,J2:J1000<>"","No Data"))

Also there is no deed to use a defined name, If the formula is in J2 you can just use =J2# in the data validation