Forum Discussion
Can you Automatically Update Formulas and Data Validation Between 2 cells in Excel Using VBA
In Excel, I have the following formula in cell D14:
=LET(
lookupValue, $D$13,
Name, VLOOKUP(lookupValue, Cust_Tbl, 2, 0),
IF(lookupValue<>"", IF(Name<>"", Name, ""), "")
)
This formula returns the name based on the contact entered in D13. I now want to extend this functionality, so that I can enter a name in D14, and the corresponding contact will appear in D13 using the lookup. Is it possible to achieve?
I want to automate this process using VBA. Specifically, if I make a change in D13, the formula in D14 should turn into searching. Similarly, if I make a change in D14, the formula in D13 should adjust accordingly.
Additionally, I have data validation applied to D13 with the formula '=Cust_List'. So, I also want the data validation in D14 to change to '=Cust_List_Name' when it will be used for search.
Thanks, and sorry for the late reply. I like your approach and it will probably work. However, following is the code that works and requires lesser steps:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ExitHandler If Not Intersect(Target, Range("D13")) Is Nothing Then ' Set formula in D14 when D13 changes Range("D14").Formula = "=LET(lookupValue,$D$13,Name,VLOOKUP(lookupValue,Cust_Tbl,2,0),IF(lookupValue<>"""",IF(Name<>"""",Name,""""),""""))" ElseIf Not Intersect(Target, Range("D14")) Is Nothing Then ' Set formula in D13 when D14 changes Range("D13").Formula = "=IF(D14<>"""",XLOOKUP(D14,Cust_Tbl[Customer Name],Cust_Tbl[Contact]),"""")" End If ExitHandler: Application.EnableEvents = True End SubIt turns out data validation didn't need to be explicitly declared everytime.
2 Replies
I haven't been able to test the code, but I think it could work.
The proof of the pudding is in the eating 🙂.
Hope this helps, if not please just ignore it.
Private Sub Worksheet_Change(ByVal Target As Range) ' Monitor changes in D13 and D14 If Not Intersect(Target, Range("D13")) Is Nothing Then If Target.Value <> "" Then ' When D13 changes, update D14 with the name lookup formula Range("D14").Formula = "=LET(lookupValue, $D$13, Name, VLOOKUP(lookupValue, Cust_Tbl, 2, 0), IF(lookupValue<>"""", IF(Name<>"""", Name, ""),""))" ' Set data validation for D13 With Range("D13").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Cust_List" .IgnoreBlank = True .InCellDropdown = True End With End If ElseIf Not Intersect(Target, Range("D14")) Is Nothing Then If Target.Value <> "" Then ' When D14 changes, update D13 with the contact lookup formula Range("D13").Formula = "=LET(lookupValue, $D$14, Contact, VLOOKUP(lookupValue, Cust_Tbl, 1, 0), IF(lookupValue<>"""", IF(Contact<>"""", Contact, ""),""))" ' Set data validation for D14 With Range("D14").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Cust_List_Name" .IgnoreBlank = True .InCellDropdown = True End With End If End If End SubThanks, and sorry for the late reply. I like your approach and it will probably work. However, following is the code that works and requires lesser steps:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ExitHandler If Not Intersect(Target, Range("D13")) Is Nothing Then ' Set formula in D14 when D13 changes Range("D14").Formula = "=LET(lookupValue,$D$13,Name,VLOOKUP(lookupValue,Cust_Tbl,2,0),IF(lookupValue<>"""",IF(Name<>"""",Name,""""),""""))" ElseIf Not Intersect(Target, Range("D14")) Is Nothing Then ' Set formula in D13 when D14 changes Range("D13").Formula = "=IF(D14<>"""",XLOOKUP(D14,Cust_Tbl[Customer Name],Cust_Tbl[Contact]),"""")" End If ExitHandler: Application.EnableEvents = True End SubIt turns out data validation didn't need to be explicitly declared everytime.
