Excel跨工作表表格联动：表1行增删时仅联动表2A列

Great question! Let's break this down clearly—first, a key limitation to note: Excel formulas cannot directly insert or delete rows because they only calculate values within cells, not modify the worksheet's structural layout (like adding/removing rows). So replacing the false branch of your existing formula won't achieve row insertion.

Feasible Solutions for Syncing Tables #

1. Use Power Query (Get & Transform) for Automated Sync #

Power Query is ideal for keeping two tables linked via Column A and syncing row changes with minimal manual effort. Here's how to set it up:

• Navigate to your Table1 worksheet, select any cell in the table, then click Data > From Table/Range to load it into the Power Query Editor.
• If you only need to sync Column A, right-click other columns and select Remove to keep only the column you want to link.
• Save the query by clicking Home > Close & Load To..., then choose Only Create Connection.
• Go to your Table2 worksheet, click Data > Existing Connections, select the saved query, and load it as a table.
• Whenever you insert or delete rows in Table1, just right-click Table2 and select Refresh—it will automatically update to match Table1's row structure, with Column A values aligned perfectly.

2. Use VBA for Real-Time Row Sync #

If you need instant, automatic sync without manual refreshes, a VBA macro can listen for changes in Table1 and adjust Table2 accordingly. Here's a sample script:

• Open the VBA Editor by pressing Alt + F11.
• In the Project Explorer, double-click the worksheet containing Table1.
• Paste this code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl1 As ListObject, tbl2 As ListObject
    Dim rowIndex As Integer
    
    ' Replace with your actual table and worksheet names
    Set tbl1 = Me.ListObjects("Table1")
    Set tbl2 = ThisWorkbook.Worksheets("Sheet2").ListObjects("Table2")
    
    ' Check if the change affects Table1's data range
    If Not Intersect(Target, tbl1.DataBodyRange) Is Nothing Then
        ' Clear existing rows in Table2 (keep header)
        If tbl2.ListRows.Count > 0 Then
            tbl2.DataBodyRange.Delete
        End If
        
        ' Sync Table1's Column A to Table2
        For rowIndex = 1 To tbl1.ListRows.Count
            tbl2.ListRows.Add
            tbl2.DataBodyRange(rowIndex, 1).Value = tbl1.DataBodyRange(rowIndex, 1).Value
        Next rowIndex
    End If
End Sub

• This macro will automatically refresh Table2's rows and Column A values whenever you insert or delete rows in Table1.

Note on Your Existing Formula #

Your current formula =IF(NOT(EXACT(J11:J14,N11)),J11,N11) works for value matching in a fixed range, but it can't handle structural changes like row insertion. With either Power Query or VBA, you won't need this formula anymore—Table2 will directly mirror Table1's Column A structure and values.

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