Excel自动化需求：当A列显示yes时，自动复制B/C列内容至D列

Hey there, let's solve this Excel automation task you've got! You want Column D to automatically pull content from B or C when Column A shows "yes" (indicating an expired date). Here are two straightforward approaches:

1. Use a Worksheet Formula (Simplest Method) #

This works if you want D to update in real-time without macros.

Scenario 1: Column A actually contains the text "yes" (not just conditional formatting display) #

Enter this formula in cell D2 and drag it down to all rows you need:

=IF(A2="yes", IF(B2<>"", B2, C2), "")

• Breakdown:
  • First checks if A2 equals "yes" (your expired flag)
  • If true: uses B2's value if it's not empty; falls back to C2 if B2 is blank
  • If false: leaves D2 empty

Scenario 2: Column A shows "yes" via conditional formatting (cell has a date, not text) #

If your "yes" is just a formatted display (the cell itself holds a date), skip checking the text and directly judge if the date is expired. Use this formula instead:

=IF(A2<TODAY(), IF(B2<>"", B2, C2), "")

• This checks if the date in A2 is earlier than today (adjust the condition if your "expired" definition is different, e.g., A2<DATE(2024,12,31) for a fixed cutoff)

2. Use VBA for Auto-Triggered Updates (More Dynamic) #

If you want D to update automatically whenever A, B, or C changes (without relying on formula drags), use this VBA script:

1. Press Alt + F11 to open the VBA Editor
2. Find your target worksheet in the left pane and double-click it
3. Paste this code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only react to changes in columns A, B, or C
    If Not Intersect(Target, Me.Range("A:C")) Is Nothing Then
        Dim affectedRow As Range
        ' Loop through each row that was changed
        For Each affectedRow In Intersect(Target, Me.Range("A:C")).Rows
            ' Check if the date in column A is expired (adjust condition as needed)
            If affectedRow.Cells(1, 1).Value < Date Then
                ' Prioritize column B; use C if B is empty
                If affectedRow.Cells(1, 2).Value <> "" Then
                    affectedRow.Cells(1, 4).Value = affectedRow.Cells(1, 2).Value
                Else
                    affectedRow.Cells(1, 4).Value = affectedRow.Cells(1, 3).Value
                End If
            Else
                ' Clear D if the date isn't expired
                affectedRow.Cells(1, 4).Value = ""
            End If
        Next affectedRow
    End If
End Sub

• Notes:
  • Save your workbook as an .xlsm file (macro-enabled) to keep the script
  • Adjust the expiration condition (< Date) if you use a different rule for expired dates
  • If you want to prioritize C over B, swap the Cells(1,2) and Cells(1,3) references

Quick Tips #

• Test the formula/VBA with a few rows first to make sure it matches your exact needs
• If B and C might both have values, tweak the logic to pick the one you need (e.g., always use B, or use the most recently updated one)

内容的提问来源于stack exchange，提问作者JACK