Excel数据验证以另一个单元格值为条件

Here's the context:

这是上下文：

Sheet 1 : 
A1 = "50255"
A2 = "Soccer players"
A3 = The cell I'm having a problem with

Sheet 2 :
A1:A5 : List of soccer plays (Ronaldo, Zidane...)

Sheet 3 :
A lot of datas in which I can possibly find my Sheet1!A1 value

I'd like to be able to do this :

我希望能够这样做：

If A1<>"" then Vlookup in Sheet 3 and print my value "50255".
If A1="" then diplay the list of possible values you can set for Sheet2!Soccer players (Ronaldo, Zidane...)

Problem : I'm able to do both things in 2 differents cells but I cannot manage to do what I want in one cell...

问题：我能够在2个不同的单元格中完成这两件事，但我无法在一个单元格中做到我想要的...

To sum up : I'd like to have either the result of my Vlookup or (if empty) a list of possible values.

总结一下：我希望得到我的Vlookup的结果或（如果为空）可能的值列表。

What I've tried in data validation:

我在数据验证中尝试过的内容：

=SI($E$31<>"";RECHERCHEV($E$31;'1 - Data players'!$G:$XFD;EQUIV('2 - OD'!$E38;'1 - Data players'!$G$6:$EX$6;0);FAUX);indirect($E$38))

Formula in English Excel:

英语Excel中的公式：

=IF($E$31<>"",VLOOKUP($E$31,'1 - Data players'!$G:$XFD,MATCH('2 - OD'!$E38,'1 - Data players'!$G$6:$EX$6,0),FALSE),INDIRECT($E$38))

Could you help me?

你可以帮帮我吗？

2 个解决方案

#1

You can put both the vlookup formula and validation into the same cell. The only problem is that if you set a value using the drop-down, it overwrites the formula. For your purposes, do you need the formula to return at a later point? If not, then the following solution may be suitable.

您可以将vlookup公式和验证放在同一个单元格中。唯一的问题是，如果使用下拉列表设置值，则会覆盖公式。出于您的目的，您是否需要在稍后返回公式？如果不是，那么以下解决方案可能是合适的。

Note that to use validation from a different sheet, you need to first apply a range name to the list of values, then refer to the range name in the data validation.

请注意，要使用其他工作表中的验证，您需要先将范围名称应用于值列表，然后在数据验证中引用范围名称。

I've set up 3 sheets as per your example. I've set "Players" as the range name for A1:A3 on Sheet2 containing the list of player names. I've then put a vlookup formula into A3:

我根据你的例子设置了3张。我将“Players”设置为Sheet2上包含播放器名称列表的A1：A3的范围名称。然后我将一个vlookup公式放入A3中：

=IFERROR(VLOOKUP(A1,Sheet3!$A$1:$B$3,2,0),"")

Then added data validation to A3 using the List option:

然后使用List选项将数据验证添加到A3：

=IF($A$1="",Players,"")

Then the drop down menu appears if I clear the value from A1:

如果我清除A1中的值，则会出现下拉菜单：

#2

Add a Worksheet_Change event to the first worksheeet (per your context) e.g. Sheet1 and then when the target cell (A1) changes then you can check the value and then either

将Worksheet_Change事件添加到第一个worksheeet（根据您的上下文），例如Sheet1然后当目标单元格（A1）发生变化时，您可以检查该值，然后查看其中之一

Add a data validation to A3 with Range("A3").Validation.Add..., or

使用范围（“A3”）向A3添加数据验证。验证。添加...，或
Insert a formula to A3 with whatever you need

无论您需要什么，都可以将公式插入A3

The example below just uses a simple list and VLOOKUP range that you can adapt to your circumstances:

下面的示例只使用一个简单的列表和VLOOKUP范围，您可以根据自己的情况进行调整：

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim blnChange As Boolean
    Dim strVlookupFormula As String

    On Error GoTo ExitFunction:

    'vlookup if target is not empty
    strVlookupFormula = "=IFERROR(VLOOKUP($A$1,$E$2:$F$4,2,FALSE),""no match"")"

    'was our cell changed
    blnChange = Not Application.Intersect(Target, Sheet1.Range("A1")) Is Nothing

    If blnChange Then
        'disable events as we are going to update cells
        Application.EnableEvents = False

        'is cell empty
        If IsEmpty(Target.Value) Then

            'remove anything from cell
            Sheet1.Range("A3").ClearContents

            'add data validation to cell
            With Sheet1.Range("A3").Validation

                .Add Type:=xlValidateList, _
                    AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, _
                    Formula1:="=Sheet1!C2:C5"

                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        Else

            'remove any validation
            Sheet1.Range("A3").Validation.Delete

            'add vlookup formula to cell
            Sheet1.Range("A3").Formula = strVlookupFormula

        End If
    End If

ExitFunction:
    ' re-enable events
    Application.EnableEvents = True

End Sub

Example with the data validation on empty A1 value:

对空A1值进行数据验证的示例：

Example with the formula with non-empty A1 value:

具有非空A1值的公式的示例：

#1