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
2
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
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
withRange("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
2
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
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
withRange("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值的公式的示例: