formula fill data validation from list without duplicated and blank

[MKLAQ]

hello
I need dynamic formula when I add in the list a new items then shows automatically without I have to specify the range with considering ignore the duplicated items or blank cells
the data validation is in b2 and the list in column a
thanks

 

[jasonb75]

Something like this?

Note that it will not be possible without the extra column. Without office 365 dynamic arrays you will also need to ensure that the formula in column B is filled down far enough to return all entries. Beyond that, your only other option would be to use vba.

Second note, the formula in A4 is only there for the purpose of testing with a blank in the middle of the list. It is not required for the rest to work.

Book1
	A	B	C
1	List	Second list	Validation
2	a	a	b
3	b	b
4		d
5	a	e
6	d
7	e
8
Sheet3

Cell Formulas
Range	Formula
A4	A4	=""
B2:B7	B2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(list)/ISNA(MATCH(list,B$1:B1,0))/(list<>""),1)),"")

Named Ranges
Name	Refers To	Cells
list	=OFFSET(Sheet3!$A$2,0,0,MATCH("zzz",Sheet3!$A:$A)-1,1)	B2:B7
secondlist	=OFFSET(Sheet3!$B$2,0,0,COUNTIF(Sheet3!$B:$B,">""")-1,1)	B3:B7

Cells with Data Validation
Cell	Allow	Criteria
C2	List	=secondlist

 

[MKLAQ]

thanks I thought it's possible do that by formula I find this way is not practical as you said I need vba
I have to wait for somebody provides me the macro does that
thanks for your try I appreciate that