The best way I can explain what I needs is this:

if A1 = "" and A3 = "", then put a data validation list in cell A2.

If A1 = "" and A3 <> "", A2 = A3.

If A1 <> "" and A3 = "", then A2 = A1

If A1 <> "" and A3 <> "", then A2 = A1I know I'm leaving out a lot of information, but if anyone could write this in VBA Code where it automatically updates when A1 or A3 are changes, I would appreciate it. I am working in Excel 2016, but I need this to work with Excel 2010 as well. I know, quite literally, nothing when it comes to coding.

message edited by srcarter2012

What do you mean by "put a data validation list in A2"? The list has to contain something. You need to tell us what that is. Is the list in a range of cells? Is it a list of hard-coded values?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

If it were possible to write this in a formula in cell A2, it would go as follows: =if(and(A1="",A3=""),Named_Range,if(and(A1="",A3<>""),A3,if(and(A1<>"",A3=""),,A1,

if(and(A1<>"",A3<>""),A1,""))))Where as "Named_Range" would make the cell a data validation list with the named range "Example 1". In every other instance, the data validation list would be deleted and would equal the respective cell.

However, I know that this is not possible (I think) without using Worksheet Change in VBA Code. I am not familiar with writing VBA Code, but I do know you can write If/Then statements and Data Validation LIsts in VBA code. I just need to make that "Formula" in VBA Code in excel.

First, you can't have a space in a Range Name, so "Example 1" will not work. I used "Example_1" in the code below. I also left in all of the Validation options in case you want to set any of them. Try this code.

Private Sub Worksheet_Change(ByVal Target As Range) 'EnableEvents if Code Error occurs On Error GoTo CleanExit 'Determine which cell was changed If Target.Address = "$A$1" Or Target.Address = "$A$3" Then Application.EnableEvents = False 'If A1 <> "", A2 = A1 If Range("$A$1") <> "" Then With Range("A2") .Validation.Delete .Value = Range("$A$1") End With 'If A1="" and A3 <> "", A2 = A3 ElseIf Range("$A$1") = "" And Range("$A$3") <> "" Then With Range("A2") .Validation.Delete .Value = Range("$A$3") End With 'If A1 and A3 = "", Set A2 Validation List Else Range("$A$2") = "Please Select" With Range("A2").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Example_1" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If End If 'EnableEvents before exiting CleanExit: Application.EnableEvents = True End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll