Forum Discussion
Trigger a cell based on the content of another cell
I am trying to have a create a few conditional formulas or triggers and I am missing the logical argument somewhere. I am trying to get cells in Column J to automatically enter "All Sources" if the selection in Column F is "Term"
I am using Data Validation on most columns and rows in this form, but these are automatic selections regardless of the remainder of the information.
I am not sure if this is best served by a rule, conditional formatting, a formula, or some other option that my brain is skipping. Thank you in advance for any guidance.
1 Reply
If column J should be blank if column F does not contain "Term", you can use a formula: in J2:
=IF(F2="Term", "All Sources", "")
and fill down.
If the user should be able to enter or select a value in column J, you cannot use a formula. You need VBA code. This will work in the desktop version of Excel for Windows/Mac, and it requires that users allow macros.
To do this:
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Intersect(Range("F2:F" & Rows.Count), Target) Is Nothing Then Exit Sub If Target.Value = "Term" Then Application.EnableEvents = False Target.Offset(0, 4).Value = "All Sources" Application.EnableEvents = True End If End Sub
