If/Then Help

[Tony_Lombardo]

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!

[#2]

@Tony_Lombardo 

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.

[#3]

Thanks. I'm not liking the error message. Can you break out option 2 for me?

[#4 · Accepted solution]

@Tony_Lombardo 

Right-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.

[#5]

@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

[#6]

@Tony_Lombardo 

It was A1 / B2. Is it now A1 / B1?

[#7]

I apologize. My brain is trying to juggle too many things at the moment. The A1/B2 example still applies, but I'm also trying to add C1/D2, E1/F2, etc. Does that make sense?

[#8]

@Tony_Lombardo 

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

[#9]

Wow, thank you for this!