☰

如何在另一张工作表上使用不相邻的单元格进行数据验证下拉菜单，并仅显示非空值？

tag-icon • tag-icon microsoft-excel-2010 data-validation
如何在另一张工作表上使用不相邻的单元格进行数据验证下拉菜单，并仅显示非空值？

我在一张工作表上有几个不相邻的单元格，我想将它们用作另一张工作表单元格中数据验证的源。但是，数据验证似乎本身不允许这样做。

因此，我尝试了一种解决方法，即将公式放入另一张表上的一些相邻单元格中，这些单元格将引用我想要的第一张表中的值。然后，我命名了第二个范围并在“数据验证源”字段中引用该名称。

现在我遇到一个问题，如果第一张工作表中的单元格之一是空白的，则第二张工作表中引用第一张工作表中的空白的单元格将显示出来，0而不是空白。我尝试通过执行类似操作来解决这个问题=IF(Sheet1!A1="","",Sheet1!A1)。然而，这并没有实际上使公式结果与空白单元格相同。

当我希望我的数据验证包含一个单元格内下拉列表时，这一切就变得很成问题。我在这里的选择似乎是0在源数据中有空白的地方在下拉列表中添加一个空行。如果空白是真正的空白，就不会发生这种情况。

那么，有没有什么办法可以解决这个问题呢？

例子：

工作表1

A1= Value1
A5= Value2
A9= Value3
A13= Value4
A17=（单元格为空白）
A21=（单元格为空白）

工作表2

A1 =Sheet1!A1（返回Value1）
A2 =Sheet1!A5（返回Value2）
A3 =Sheet1!A9（返回Value3）
A4 =Sheet1!A13（返回Value4）
A5 =Sheet1!A17（返回0）
A6 =IF(Sheet1!A21="","",Sheet1!A21)（显示空白）

Sheet2!A1:A6名为Validation。在 处Sheet3!A1，数据验证与源和单元格内下拉菜单一起应用=Validation。单元格内下拉菜单显示：

值1
值2
值3
值4
0

（含空白单元格）

在这些条件下，我正在寻找一种配置，该配置将导致下拉菜单仅在填充时Sheet3!A1显示，同时保持可用。或者，下拉菜单应仅在填充时显示，同时保持可用。Sheet2!A1:A4Sheet2!A5:A6Sheet3!A1Sheet1!A1,Sheet1!A5,Sheet1!A9,Sheet1!A13Sheet1!A17,Sheet1!A21

看来我需要一种方法来：

  • Sheet1在我的数据验证源中直接处理不相邻的单元格，
    或者
  • Validation将范围内的单元格获取Sheet2到实际上当目标Sheet1是空白时，返回空白单元格。

答案1

使用您原帖中描述的方法如何使数据验证下拉菜单排除空白。

将其与以下方法结合起来：

Public Sub ClearDataValidation(destrng As Range)
   destrng.Validation.Delete
End Sub

Public Sub LoadDataValidation(srcrng As Range, destrng As Range)
   'Verify a 1x1 sized Range was passed
   If destrng.Rows.Count <> 1 Or destrng.Columns.Count <> 1 Then
      InvalidValue destrng.Worksheet, "LoadDataValidation", _
            "Range: " & destrng.name & " was passed to method. This method expects a " & vbCrLf & vbCrLf & _
            " 1 Row x 1 Column Range to be passed.  Anything outside of the 1x1 " & vbCrLf & vbCrLf & _
            "size will result in invalid conditions"
      Exit Sub
   End If

   With Range(destrng.Address).Validation
      .Delete
      .Add xlValidateList, xlValidAlertStop, xlBetween, DistinctValues(srcrng)
   End With
End Sub

用法：

LoadDataValidation Range("Table1[column1]"), Range("destinationCell")

相关内容