For your range L4 to L27
Use this
Then copy down![]()
M4=IF(ROW()-3>COUNTA($L$4:$L$27),"",INDEX($L$1:$L$27,SMALL(IF(ISBLANK($L$4:$L$27),"",ROW($L$4:$L$27)),ROW()-3)))
Define name , eg ABC
Then make your validation list , source "=ABC"![]()
=INDIRECT(CONCATENATE("$M$4:$M",COUNTA($M$4:$M$27)-COUNTIF($M$4:$M$27,"")+3))
Bookmarks