Sorting data on multiple sheets

The issue stems from how standard SUM functions work. When you write a formula like =SUM(Week1!B2), Excel looks at the physical position of the cell (Row 2, Column B). When you sort the sheet, the person’s name moves to a new row, but the formula stays stuck on Row 2. Consequently, it ends up summing up whoever moved into that row, rather than the specific person you intended.

Here are a two of reliable ways to fix this and make your workbook stable, even when sorting.

Option 1: The Quick Fix (Using SUMIFS)

Instead of telling Excel where the cell is (e.g., B2), you need to tell Excel who the cell belongs to. You can do this by swapping your SUM functions for SUMIFS.

SUMIFS looks at a whole column, finds a specific name (or criteria), and sums the corresponding number—regardless of which row it is in.

Try this formula structure on your Monthly Overview sheet:
=SUMIFS(Week1!$C:$C, Week1!$A:$A, "John Doe")

• Week1!C:C: The range containing the numbers you want to add up (e.g., Sales).
• Week1!A:A: The range containing the Names.
• "John Doe": The specific person you are looking for (or reference the cell containing the name on your overview sheet).

Now, when you sort Week 1, the formula will hunt for "John Doe" anywhere in column A and sum his numbers correctly.

Option 2: The "Pro" Solution (Consolidating Data)

Since you mentioned you are using Copilot and Excel for the Web, I strongly recommend a structural change that will make your life infinitely easier for analysis.

Instead of having 5 separate sheets for 5 weeks, try combining them into one single Master Table (let's call it "AllData").

1. Add a column called "Week Number" (1, 2, 3, 4, 5).
2. Stack all your team's data vertically in this one sheet.

• Sorting is safe: You can sort by Name, Week, or Sales, and the rows stay intact because the data is in a "database" format.
• Copilot Magic: Copilot works 10x better on a single structured table than on 5 disjointed sheets. You can simply ask Copilot: "Show me a summary of sales by person across all weeks" or "Create a pivot table showing performance trends," and I think (is untested) it will generate it instantly.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.