☰

基于相邻单元格的 Microsoft Excel 验证

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 data-validation
基于相邻单元格的 Microsoft Excel 验证

我有两个相邻的列，分别名为Country和Rating。该Country列有一个下拉列表，用于选择 DVD 要发送到哪个国家/地区。它使用验证list来创建下拉列表：

=$AN$31:$AN$53   # [US, CA, JP, etc.]

我需要做的是Rating根据列的值显示一个验证列表Country。例如，

if Country=US, Rating dropdown = [G, PG, PG-13, R]
if Country=CA, Rating dropdown = [G, PG, 14A, 18A, R]

我该如何在 Excel 中执行此操作？

答案1

好的。所以我通过 VBA 完成了此操作。假设您的初始验证在 D2 中。将其放在 Sheet1 的代码中（右键单击 - 查看代码）

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(True, True) = "$D$2" Then
        Select Case Target
            Case "A"
                Call Macro1
            Case "B"
                Call Macro2
            Case "C"
                Call Macro3
            Case "D"
                Call Macro4
            Case "E"
                Call Macro5
            Case Else
                'Do nothing
        End Select
    End If

End Sub

一旦您在工作表的代码中拥有它，创建一个模块并使用它：

Sub Macro1()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$A$9:$A$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
       End With
End Sub


Sub Macro2()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$B$9:$B$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub


Sub Macro3()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$C$9:$C$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub



Sub Macro4()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$D$9:$D$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub


Sub Macro5()
Range("E2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$E$9:$E$13"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

现在您需要做的就是更改列表坐标，一切都会完美运行。

相关内容