简单的方法来删除空白单元格动态下拉列表Excel [英] Simple way to remove blank cells dynamic dropdown list Excel

问题描述

每当我执行从属动态下拉列表时，都会在下拉列表中看到一堆空白单元格，我搜索了许多说明如何删除它们的主题，同时添加了两个额外的范围，例如她的 http://blog.contextures.com/archives/2014/02/27/dynamic-list-with-blank-cells/

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?

所有包含空白单元格的下拉列表都是去数据验证并写在源代码=MYCode中，然后我命名了包含诸如MyCode这样的代码的列表，然后我检查了忽略空白大小写(甚至似乎没用)

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<>"")))

即使代码行相当复杂，它也完成了我所需的操作，而无需添加2个额外的单元格.

It did what I needed without the need of adding 2 extra cell, even though the line of code is rather complex.

这篇关于简单的方法来删除空白单元格动态下拉列表Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！