☰

如何在验证列表中组合范围和确定值？

tag-icon • tag-icon microsoft-excel-2013
如何在验证列表中组合范围和确定值？

我有一个带有下拉列表的单元格（使用“数据验证”）。

该列表来自另一个工作表中的命名范围。

我想要下拉菜单显示命名范围和另一个预定义值。例如，如果范围如下所示：

1
2
3
4

我想要下拉菜单显示：

1
2
3
4
17 (my predefined value)

如何实现这一点？

答案1

如果您想使用验证数据来创建列表，这是不可能的，您将收到错误消息： 您不能使用引用运算符（例如并集、交集和范围）或数组常量作为条件格式标准。

因此我建议您尝试使用代码作为解决方法来帮助您解决这个问题。

Sub SetDataValidation() '

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")

Dim rng As Range

'get range of named range

Set rng = WS.Range("NUMBER")

Dim ARR As Variant

ReDim ARR(1 To 1) As Variant

'add cell value from named range to an array

For i = 1 To rng.Cells.Count

ARR(UBound(ARR)) = rng.Cells(i).Value

ReDim Preserve ARR(1 To UBound(ARR) + 1) As Variant

Next i

'add your predefined value to the array

ARR(UBound(ARR)) = 17

'set validation for B1 with the combined array

With WS.Range("B1").Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(ARR, ",")

End With

End Sub

在我的示例中，我将范围命名为 NUMBER 并得到结果：

在此处输入图片描述

答案2

我遇到了和你类似的问题。我有一个Dyn_ParamList_KillDate包含日期列表的命名范围1/1/2018, 1/2/2018, etc...。此动态命名范围由进程填充并更改大小。问题是有时列表会为空，而下拉列表为空会导致问题。

Range_Writeback_ModelParamDate因此，对于我的解决方案，我创建了另一个始终具有值的命名范围。然后我将数据验证列表从 更改=Dyn_ParamList_KillDate为=IF(Dyn_ParamList_KillDate<>"", Dyn_ParamList_KillDate, Range_Writeback_ModelParamDate)。

一旦我添加了第二个范围，确保它始终有一个值，并将下拉公式更改为使用 IF 并从任一范围中提取 - 我开始正确地根据两个范围获得结果。我希望这对某人有所帮助。

这个解决方案的想法来自于这里：https://www.mrexcel.com/forum/excel-questions/754454-you-may-not-use-reference-operators-array-constants-data-validation.html

相关内容