How can I sort data with formulas in Excel?

Question

Hi,

I'm trying to sort data that is connected to another list. For example:

In column ABCD I have the names in A in alphabetical order and column B is actually a formula [=c2*2] and copy and pasted down to 5.

-----A---------B---------C---------D
1 Player--Points--Wins--Losses
2 Abe ------ 2 -------- 1 ------- 5
3 Bob ------ 8 -------- 4 ------- 2
4 Cal ------- 6 -------- 3 ------- 3
5 Joe ------- 8 -------- 4 ------- 2

Because I want to keep the names in alphabetical order to easily update the stats (I actually have about 30 names), I created another list on the same worksheet, only 5 columns over to sort by Point value.

Columns IJKL look exactly the same except I used formulas instead of numbers so that I would only need to enter the stats once. So my column C looks like this (then I copy and pasted the formulas for the other rows)

------ I ---------- J -------- K -------- L
1 Player -- Points -- Wins - Losses
2 Abe --- [=k2*2] - [=c2] -- [=d2]
3 Bob --- [=k3*2] - [=c3] -- [=d3]
4 Cal ---- [=k4*2] - [=c4] -- [=d4]
5 Joe ---- [=k5*2] - [=c5] -- [=d5]

Here is my problem. I highlight the columns IJKL and I try to sort the values in the "Points" heading column from highest to lowest so that the names would ideally be in order from most points to least points. The names actually go in that order, but then the values stay exactly where they are and the Point values no longer sync with the player. After the sort, it would look like this:

What you see:----------------------------------- Looking at the cells:
------I -------- J -------- K ------ L
1 Player Points Wins - Losses
2 Bob ------ 2 -------- 1 ------- 5 -------| 2 - Bob --- [=k2*2] - [=c2] -- [=d2]
3 Joe ------- 8 -------- 4 ------- 2 -------| 3 - Joe --- [=k3*2] - [=c3] -- [=d3]
4 Cal ------- 6 -------- 3 ------- 3 -------| 4 - Cal ---- [=k4*2] - [=c4] -- [=d4]
5 Abe ------ 8 -------- 4 ------- 2 -------| 5 - Abe --- [=k5*2] - [=c5] -- [=d5]

The formulas (stats for the player) don't stay with the player. After the sort, I look at the cells and see that though the players' names have changed to the correct order from most points to least points, the formulas haven't moved.

And finally the question: How do I sort this data so that the formulas will always stay with the name like this:?

------ I ---------- J --------- K ------- L
1 Player -- Points -- Wins Losses
2 Bob --- [=k3*2] - [=c3] -- [=d3]
3 Joe ---- [=k5*2] - [=c5] -- [=d5]
4 Cal ---- [=k4*2] - [=c4] -- [=d4]
5 Abe --- [=k2*2] - [=c2] -- [=d2]

PS: I don't need it to automatically sort.

Answer 1

you need to rethink the whole concept of what you're doing. By putting formulas in IJKL that point back to the original cells, sorting by the highest value in IJKL isn't going to work because the formulas don't contain the actual values, they only contain references to the cells that have the actual values.



A couple of options:

1. why not just sort the original values (in ABCD) by the point values. Then when it's time to update the data, resort back by name again.



2. use the pivot table option. Create a "pivot table" based on the original data in ABCD. With a pivotal table you can sort/display the data anyway you want without running into the problem you currently have. Then after you add/update the data in ABCD, simply "refresh" the pivot table with the updated info. If you haven't done a pivot table before I recommend you google it and learn about them. It's a VERY powerful tool/option in Excel that can save you tons of time once you figure out how they work.

Answer 2

Excel Sort Formula

Answer 3

If you're scouting about for an application that is dependable and stylish all at once you then would definitely get this system https://tr.im/UfcDw Patriot Self Defense .

Utilizing the Patriot Self Defense is extraordinarily simple since is made in order that everybody can use it. This system not merely include a book that understand you wherever will be the wickless level of one's opponent to be able to defend your self but also you can have the films where you are able to see, firsthand steps to make the transfer which are discussed in the book.

Answer 4

Take heed of Satishfreeman but pay specified concentration to that opting for the whole desk part. Not like a database desk like access where all of the records are sorted, a spread sheet table style will happily sort just one column or row and put the whole thing out of sync! Make a backup first.