Oct 22 2021 06:32 AM
I have two fields, DATE PAID (A1) and AMOUNT DUE (B2). Only one of these fields should be populated at a time. For example, there would be no DATE PAID (A1) entry if there is an AMOUNT DUE (B2). And there would be no AMOUNT DUE (B2) if there was a DATE PAID (A1).
Can someone provide a formula for B2 to accommodate this IF/THEN scenario? I'm thinking that any entry in A1 should result in a zero sum for B2. Much appreciated!
Oct 22 2021 08:03 AM
You cannot do this with a formula.
One option is to use Data Validation:
Select A1.
On the Data tab of the ribbon, click Data Validation.
Select Custom from the Allow drop-down.
Enter =B2="" in the Formula box.
Activate the Error Alert tab.
Enter an appropriate error message.
Click OK.
Select B2.
On the Data tab of the ribbon, click Data Validation.
Select Custom from the Allow drop-down.
Enter =A1="" in the Formula box.
Activate the Error Alert tab.
Enter an appropriate error message.
Click OK
Another option is to use VBA to clear B2 when the user enters something in A1 and vice versa, but that might be overkill.
Oct 22 2021 08:13 AM
Oct 22 2021 08:25 AM
SolutionRight-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
If Range("A1").Value <> "" Then
Application.EnableEvents = False
Range("B2").ClearContents
Application.EnableEvents = True
End If
End If
If Not Intersect(Range("B2"), Target) Is Nothing Then
If Range("B2").Value <> "" Then
Application.EnableEvents = False
Range("A1").ClearContents
Application.EnableEvents = True
End If
End If
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Oct 22 2021 09:24 AM
@Hans VogelaarThat worked perfectly!
I have multiple fields where I need to apply that, but when I re-paste the code (with field modifications) under the existing code it erases the existing code. Is there a way to do multiple fields? For example. A1/B1 (which we did), C1/D1, E1/F1, etc
Oct 22 2021 12:23 PM
Oct 22 2021 12:56 PM
Like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Row = 1 And Target.Column Mod 2 = 1 Then
If Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(1, 1).ClearContents
Application.EnableEvents = True
End If
End If
If Target.Row = 2 And Target.Column Mod 2 = 0 Then
If Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(-1, -1).ClearContents
Application.EnableEvents = True
End If
End If
End Sub
Oct 22 2021 08:25 AM
SolutionRight-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
If Range("A1").Value <> "" Then
Application.EnableEvents = False
Range("B2").ClearContents
Application.EnableEvents = True
End If
End If
If Not Intersect(Range("B2"), Target) Is Nothing Then
If Range("B2").Value <> "" Then
Application.EnableEvents = False
Range("A1").ClearContents
Application.EnableEvents = True
End If
End If
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.