Forum Discussion
Sorting data on multiple sheets
Hi,
I'm using Excel to track data from my team for analysis. I have 5 sheets in my workbook one for each week and a monthly overview.
each week totals all data and totals are added to overview using SUM function, however when i sort a column, let's say i sort column a (Name) A-Z, all the data for each person on the monthly report changes. the same happens on the weekly sheets if i change the order of a column everything get mixed up so that each persons stats change. is there a way to stop this from happening? I want to be able to arrange it so that all the data in a row stays the same on each sheet
2 Replies
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").
- Add a column called "Week Number" (1, 2, 3, 4, 5).
- 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.
Donsclan So may I assume that you are using a regular SUM function that points to cells for specific individuals in the weekly sheet? If so, look into using SUMIF or SUMIFS instead. These functions allow you to sum values in an entire range based on criteria in another range or other ranges.
But perhaps better to get rid of the weekly sheets all together and create one table with whatever you are tracking, in a tabular lay-out. Much easier to analyse/summarise than data in separate sheets.
