下拉公式只显示空白（公式下拉数据不变怎么办）

在写文章之前，首先感谢两位网友：山姆达叔和手机用户6199742031，提出了上篇文章介绍下拉菜单存在的不完美之处

山姆达叔：提出如何让下拉菜单，下拉的时候，自动去掉空白，有多少内容，显示几行

2，手机用户6199742031：提出上篇公式在应用中，出现数据有效性无法使用其他工作表内容的情况

本篇根据网友要求，对上篇进行完善，并总结常用下拉菜单的3种方法：

下拉公式只显示空白,公式下拉数据不变怎么办(1)

方法1：用OFFSET偏移法实现多级下拉菜单设置：

具体步骤这里不多讲，请参考分享，这里主要讲上面网友提出的问题：

1，山姆达叔建议处理方式：将原来的公式修改为

=OFFSET(物料主文件!$A$1,1,MATCH($A1,物料主文件!$B$1:$G$1,0),COUNTA(OFFSET(物料主文件!$A$1,1,MATCH($A1,物料主文件!$B$1:$G$1,0),10,1)),1)下拉的时候，就去掉了空置

公式解释：原来做下拉菜单的公式：=OFFSET(物料主文件!$A$1,1,MATCH($A1,物料主文件!$B$1:$G$1,0),10,1)，这要问题出现在这个10上，offset（偏移参考点，向下几行开始，向右几列开始，内容取向下偏移到多少行，内容取向右偏移多少列），因为这个公式，我们直接取了10行，所以有内容，没有内容，都会出来，主要思路我们要讲10，替换为有内容的单元格的个数就行了，这里用到函数counta,外套到公式外边，得出有内容的格子的个数，而后将公式复制到原来公式第四个参数即可。上面黑色加粗地方，相当于原来的10，只是替换为格子的个数了。

2，手机用户6199742031问题原因：

=OFFSET(物料主文件!$A$1,1,MATCH($A1,物料主文件!$B$1:$G$1,0),COUNTA(OFFSET(物料主文件!$A$1,1,MATCH($A1,物料主文件!$B$1:$G$1,0),10,1)),1)在这个公式中，我们将其加入数据有效性的时候，引用到了其他工作表的内容，就是物料主文件!$A$1，这个在2010以及以上版本中，可以直接这样写，没错，但是如果亲们是2007或是2003，是不支持这样写的，2007以及以下，在用公式做数据有效性的时候，举例要达到A=C,必须先中间弄一个值，A=B B=C，通过这种迂回，达到目的，解决方法：

公式：定义名称，将物料主文件!$A$1名称定义为a；将物料主文件!$B$1:$G$1名称定义为b，而后公式就可以写作

=OFFSET(a,1,MATCH($A1,b,0),COUNTA(OFFSET(a,1,MATCH($A1,b,0),10,1)),1),这个才是OFFSET公式偏移有效性的最佳方案，解决了上面两个网友的问题

下拉公式只显示空白,公式下拉数据不变怎么办(2)