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 )
After some more research I found a solution. In the cell where my information is filled I added a name using the name Manager and I added this formula that I adapted from this article:
=DropList!$J$1:INDEX(DropList!$J$1:$J$10000,SUMPRODUCT(--(DropList!$J$1:$J$10000<>"")))
It did what I needed without the need of adding 2 extra cell, even though the line of code is rather complex.