# Why Excel shuffles data in a table after ordering?

Copper Contributor

# Why Excel shuffles data in a table after ordering?

Hello, I have a problem understanding excel behaviour and hope you can help me with it.

I have a table with next columns: ID, Points, Tasks Received, Tasks Finished, Task Finishing Rate, Multiplicator (depends on the rate), Final_Point (Points * Multiplicator).

In the columns Received and Finished are formulas SUMIF that refer to another table and  summing up values if ID in the referred table is equal to the ID in the table row. For example in R2 cell the formula is:

=SUMIF(Referred_Table!A:A;The_Main_Table!B2;Referred_Table!C:C)

After the rate is found, I define multiplicator and calculate final points.
Then I need to order ID by final points column and here the strange things start.
I use Custom Sort by the Final Points column by value from the highest to the lowest. However performing this Excel shuffles values in Received and Finished columns such that each ID has Rate calculated for another ID.

For example the first row ID (578503) had 23/33 (Finished/Received) but after the Sorting has 19/38.
It think that happens because Sorting does not influence links in Formulas. Such as ID 578503 was in B6 cell and the formulas in Received and Finished were referring B6 cell doing SUMIF. After the Sorting the ID 578503 rised to B2 cell, but the formulas in the Finished and Received columns continuing referring B6 cell as before, so ID 578503 has values in the columns for ID 1200124 after sorting.

How can I fix it? What link should I do in the table for the formulas to be changed according to sorted ID?

Thanks and Regards

1 Reply

# Re: Why Excel shuffles data in a table after ordering?

Does it help if you use structured table references? For example, instead of referring to B2, refer to [@Responsável] ?

P.S. You do sort the entire table, don't you, and not just the Pontos_Finais column?