Simple way to remove blank cells dynamic dropdown list Excel

Question

Whenever I do a dependent dynamic drop down list , I see a bunch of blank cell in the drop-down list , I search many topics that explain how to remove them while by adding two additional ranges like explained her http://blog.contextures.com/archives/2014/02/27/dynamic-list-with-blank-cells/

but my question is: Is there anyway to avoid blank cell or remove them using a simple approach without the need of two additional ranges or a complex formula?

the drop down list that contains blank cell all I did is go to data validation and wrote in source =MYCode then I named the list that contains the codes like that MyCodeand I checked ignore blank case (even tho It seems to be useless )

Answer 1

There is another way. Create a dynamically-expanding named range. Then use the range to define the data validation list.

To create your dynamically-expanding range, insert this in the named range box and give it a name:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

$A$1 should be replaced with the top cell of your range. $A$A should be replaced with the column(s) the range is in.

OFFSET points the named range at a range of cells. COUNTA() is in the fourth position of the OFFSET formula, which sets the height of the range. It counts the number of non-blank cells. As a result, when you add a value, the fourth value of the OFFSET formula increases and you get an expanding range.

Note, this does not work if your named range has blank cells interspersed.

OFFSET formula from excel-easy.com.