Excel List from non-empty values of a column -

Excel List from non-empty values of a column -

i want create list non-empty values of column.

i want hence utilize list dropdown list in other sheets.

the values in list dynamic can't re-create these values in place.

when select values column , create list it, when utilize dropdown list, have empty values in list.

is there way, without using macros, automatically remove empty entries list?

i don't want remove empty cells column because part of table.

thanks.

the easiest way have (hidden) column values want in drop downwards list. if define (dynamic) named range of source info want list, next formula should entered in cell b2 , copied downwards required:

=index(list, match(0, if(max((countif($b$1:b1, list)=0)*((list<>"")*(countif(list, ">"&list)+1)))=(if((list<>""), countif(list, ">"&list)+1, "")), 0, ""), 0))

it sort list.

this info taken next site, has step step instructions.

http://www.get-digital-help.com/2009/05/25/create-a-drop-down-list-containing-only-unique-distinct-alphabetically-sorted-text-values-using-excel-array-formula/

excel excel-formula