如何基于输入的票号在Excel工作表间自动同步特定数据

Got it, let's sort out this two-way sync between your Excel worksheets so you can ditch the manual duplicate entries. We'll split this into two parts: pulling data from "Invoice #" to "Job Tickets" automatically, and pushing updates from "Job Tickets" back to "Invoice #" when you edit entries.

1. Auto-Fill Data from "Invoice #" to "Job Tickets" #

When you type a ticket number in column F of "Job Tickets", this will automatically pull matching data from columns A, C, D of "Invoice #" into the same row.

• Step 1: Open your "Job Tickets" worksheet.
• Step 2: In cell A2 (assuming your first data row is row 2), paste this formula:

  =IFERROR(XLOOKUP($F2,'Invoice #'!$A:$A,'Invoice #'!$A:$A,""), "")
  

• Step 3: In cell C2, use this formula:

  =IFERROR(XLOOKUP($F2,'Invoice #'!$A:$A,'Invoice #'!$C:$C,""), "")
  

• Step 4: In cell D2, enter:

  =IFERROR(XLOOKUP($F2,'Invoice #'!$A:$A,'Invoice #'!$D:$D,""), "")
  

• Step 5: Drag the small fill handle (bottom-right corner of the cell) down to apply these formulas to all rows you need.

Quick note for older Excel versions: #

If XLOOKUP isn't available in your version, swap it out for VLOOKUP:

=IFERROR(VLOOKUP($F2,'Invoice #'!$A:$D,1,FALSE), "") // For column A
=IFERROR(VLOOKUP($F2,'Invoice #'!$A:$D,3,FALSE), "") // For column C
=IFERROR(VLOOKUP($F2,'Invoice #'!$A:$D,4,FALSE), "") // For column D

The IFERROR part just ensures you get a blank cell instead of an error message when no matching ticket number is found.

2. Sync Updates from "Job Tickets" Back to "Invoice #" #

Formulas alone can cause circular reference issues for two-way sync, so we'll use a simple VBA macro to automatically update "Invoice #" when you edit entries in "Job Tickets".

• Step 1: Press Alt + F11 to open the VBA Editor.
• Step 2: In the left Project Explorer pane, find and double-click the "Job Tickets" worksheet.
• Step 3: Paste this code into the code window that pops up:

  Private Sub Worksheet_Change(ByVal Target As Range)
      Dim wsInvoice As Worksheet
      Dim matchRow As Variant
      Dim ticketNum As String
      
      ' Set reference to your "Invoice #" worksheet
      Set wsInvoice = ThisWorkbook.Worksheets("Invoice #")
      
      ' Only run if the edited cell is in columns A, C, or D (the columns we need to sync)
      If Not Intersect(Target, Me.Range("A:A,C:C,D:D")) Is Nothing Then
          ticketNum = Me.Range("F" & Target.Row).Value
          
          If ticketNum <> "" Then
              ' Find the matching ticket number in "Invoice #" column A
              matchRow = Application.Match(ticketNum, wsInvoice.Range("A:A"), 0)
              
              If Not IsError(matchRow) Then
                  ' Turn off events temporarily to avoid infinite loops
                  Application.EnableEvents = False
                  
                  ' Update the corresponding cell in "Invoice #"
                  Select Case Target.Column
                      Case 1 ' Column A in Job Tickets
                          wsInvoice.Range("A" & matchRow).Value = Target.Value
                      Case 3 ' Column C in Job Tickets
                          wsInvoice.Range("C" & matchRow).Value = Target.Value
                      Case 4 ' Column D in Job Tickets
                          wsInvoice.Range("D" & matchRow).Value = Target.Value
                  End Select
                  
                  Application.EnableEvents = True
              End If
          End If
      End If
  End Sub
  

• Step 4: Close the VBA Editor and save your workbook as a .xlsm file (this is required for files with macros).

How this works: #

Every time you edit a cell in columns A, C, or D of "Job Tickets", the macro checks the ticket number in column F of that row. It finds the matching row in "Invoice #" and updates the corresponding column automatically. We turn off events briefly to stop the macro from triggering itself when it updates the "Invoice #" sheet.

Key Tips to Keep in Mind: #

• The ticket number must be a unique identifier (column A in "Invoice #" and column F in "Job Tickets" need to match exactly for sync to work).
• When you open the workbook, Excel will prompt you to enable macros—you'll need to allow them for the sync to function.
• Test with a few dummy entries first to make sure everything works as expected before using it with real data!

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