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

Do you think SpaceX laying off some of its workforce will help it succeed?

Discuss in The Lounge

Poll History