动态新增Employee Sheet行数据求和：自动同步至Reports Sheet计算

Hey there, let's solve this problem where you need to automatically include every new EmployeeX sheet in your Reports sheet's totals—no manual formula updates every time a new employee is added. Below are two practical solutions tailored to different Excel versions and preferences:

Solution 1: Dynamic Array Formula (Excel 365/2021, No Macros Needed) #

This is my go-to for modern Excel because it's clean and auto-updates with minimal effort.

Assume every EmployeeX sheet has an identical structure, and you want to sum row 5 (e.g., "Monthly Sales") across all of them. In your Reports sheet (say cell B2), use this formula:

=SUM(IFERROR(INDIRECT("Employee"&SEQUENCE(100)&"!A5"),0))

Formula Breakdown: #

• SEQUENCE(100) generates numbers 1 to 100 (adjust this number to cover how many employees you might add in the future)
• INDIRECT("Employee"&...&"!A5") pulls the value from cell A5 of each EmployeeX sheet
• IFERROR(...) returns 0 for any EmployeeX sheet that doesn't exist yet, so you won't get #REF! errors
• SUM() adds up all valid values from existing Employee sheets

If you need to sum an entire range of rows (like rows 5 to 10), use this dynamic array formula to auto-fill totals for each row:

=BYROW(A5:A10, LAMBDA(row, SUM(IFERROR(INDIRECT("Employee"&SEQUENCE(100)&"!"&CELL("address", row)), 0))))

Just enter this in the top cell of your totals column, and Excel will automatically spill the results down to match your rows.

Solution 2: VBA Macro (All Excel Versions, Fully Automated) #

If you want zero manual intervention even when adding new sheets, a macro is the way to go. This will update your Reports sheet the second a new EmployeeX sheet is created.

Step-by-Step Setup: #

1. Press Alt + F11 to open the VBA Editor
2. In the left Project Explorer, double-click ThisWorkbook
3. Paste this code into the code window:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    ' Check if the new sheet starts with "Employee"
    If Left(Sh.Name, 8) = "Employee" Then
        Dim wsReport As Worksheet
        Set wsReport = ThisWorkbook.Worksheets("Reports")
        
        ' Find the next empty column in Reports for the new employee's data
        Dim nextCol As Integer
        nextCol = wsReport.Cells(1, wsReport.Columns.Count).End(xlToLeft).Column + 1
        
        ' Set the column header to the new sheet's name
        wsReport.Cells(1, nextCol).Value = Sh.Name
        
        ' Example: Sum row 5 from the new Employee sheet into Reports row 2
        wsReport.Cells(2, nextCol).Formula = "=" & Sh.Name & "!A5"
        
        ' Optional: Update the total sum formula (adjust range as needed)
        wsReport.Cells(2, nextCol + 1).Formula = "=SUM(B2:" & Chr(64 + nextCol) & "2)"
        wsReport.Cells(1, nextCol + 1).Value = "Total"
    End If
End Sub

4. Save your workbook as an .xlsm file (since it contains macros)

Bonus: Initialize Existing Employee Sheets #

If you already have Employee1, Employee2, etc., run this macro to populate their data into Reports in one go:

Sub InitializeExistingEmployees()
    Dim wsReport As Worksheet
    Set wsReport = ThisWorkbook.Worksheets("Reports")
    
    ' Clear old summary data (adjust range as needed)
    wsReport.Range("B2:Z100").ClearContents
    
    Dim ws As Worksheet
    Dim currentCol As Integer
    currentCol = 2
    
    For Each ws In ThisWorkbook.Worksheets
        If Left(ws.Name, 8) = "Employee" Then
            ' Set column header
            wsReport.Cells(1, currentCol).Value = ws.Name
            ' Sum row 5 from the Employee sheet
            wsReport.Cells(2, currentCol).Formula = "=" & ws.Name & "!A5"
            currentCol = currentCol + 1
        End If
    Next ws
    
    ' Add total sum column
    wsReport.Cells(1, currentCol).Value = "Total"
    wsReport.Cells(2, currentCol).Formula = "=SUM(B2:" & Chr(64 + currentCol - 1) & "2)"
End Sub

To run this, press F5 in the VBA Editor while the code is selected.

Key Notes #

• Consistent Structure is Critical: Make sure every EmployeeX sheet has the exact same layout (same rows/columns for the data you're summing)
• For the dynamic array method, increase SEQUENCE(100) to a number that's definitely larger than the maximum employees you'll add
• Macro-enabled workbooks need to be saved as .xlsm and macros enabled when opening

内容的提问来源于stack exchange，提问作者Dženis H.