Easy way to remove dynamic drop-down lists of empty Excel cells

Question

Whenever I make a dependent dynamic drop-down list, I see an empty cell in the drop-down list, I look for a lot of topics that explain how to remove them by adding two additional ranges, for example, explained to her http://blog.contextures.com/ archives / 2014/02/27 / dynamic-list-with-blank-cells /

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

the drop-down list that contains the empty cell, all I did was go to the data check and write in the source =MYCode , after which I named the list containing codes like MyCode , and I checked to ignore the empty case (even tho It seems useless )

Answer 1

After some more research, I found a solution. In the cell where my information is populated, I added the name using the manager's name, and I added this formula, which I adapted from in this article :

 =DropList!$J$1:INDEX(DropList!$J$1:$J$10000,SUMPRODUCT(--(DropList!$J$1:$J$10000<>""))) 

He did what I needed, without the need to add 2 additional cells, although the line of code is rather complicated.

Answer 2

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

To create a dynamically expanding range, paste this into the named range field 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) that the range is in.

OFFSET indicates the specified range in 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-empty cells. As a result, when you add a value, the fourth value of the OFFSET formula increases, and you get an expanding range.

Please note: this does not work if your named range contains empty cells.

The OFFSET formula from excel-easy.com .