Excel 2007 : Problems sorting data - sort causes the data to change

[somer]

Hi,

I'm new to this forum and have an issue I haven't been able to find a solution to in Excel's help - or elsewhere. It's probably because I'm not that experienced with Excel, but what I'm trying to do seems so simple that it's been perplexing to keep running into problems trying to perform simple sorts.

My spreadsheet is very simple. I enter numerical data and several simple calculations are performed on the numbers to populate various other cells. Below the main data, I have lists with columns of data from these calculations that I need to sort from highest to lowest. But, much to my chagrin, no matter how I try to sort this data, (I've tried with tables, manually, etc.) the result is that all the values change when the sort is done.

The cells that are being sorted have values from other cells (and show functions of "=E241", for example, when highlighted). The correct values appear in these cells until a sort is attempted. Then, the values change, with many becoming zero. Does this sound familiar to anyone? What do I need to do to this data to make it sortable?

Ideally, these lists of data would auto-sort to form a constantly updated "leader board" whenever data is entered or changed in the main area. But at my current level, that may be asking too much. Any advice would be greatly appreciated!

Thanks!

[MarvinP]

Hi somer and welcome to the forum,

Do you have blank rows or columns in your table of data. That could create a problem with sorting.

It would be better for us if you could attach a sample of what you are talking about. Click on the "Go Advanced" below the message area and then on the Paper Clip Icon above the advanced message area to attach a file.

[somer]

Hi Marvin,

Thanks for the quick response! If you click of the "Leaders" link (top right) in the attached file, it takes you to the 12 lists I need to sort high to low. There are no blank cells within the lists, but some values are zero. These values change as I enter new data. Is there a way I can sort these "leaders" lists or even better, have them auto-sort as new data is entered?

Thanks again for helping!

[MarvinP]

Hi somer,

I have the answer. It is a problem with relative vs absolute cell references. That sounds so official. I didn't know what that was either when spreadsheets came out. More basically you need dollar signs in front of the numbers. Here is a good site to explain it http://www.cpearson.com/excel/relative.aspx

Here is what was happening. Lets take your formula in cell D5 of = D35. This tells excel to go down 30 rows in the column D and bring back that 721 number. If you move this formula down a cell to D6 it would bring back what was in D36. You need to put a dollar sign in front of the 35 so your formula should read
Cell D5 = D$35. Now when you move the A's At Bats up or down in the sorted list it always pulls the value from row 35.

I've fixed all your formulas and have attached the workbook.

ALSO - you needed to remove the blank column (or part of it) between the team names and their stats. I've moved hitting and pitching team names to column B instead of A to acomplish this.

[somer]

Hi somer,

I have the answer. It is a problem with relative vs absolute cell references. That sounds so official. I didn't know what that was either when spreadsheets came out. More basically you need dollar signs in front of the numbers. Here is a good site to explain it http://www.cpearson.com/excel/relative.aspx

Here is what was happening. Lets take your formula in cell D5 of = D35. This tells excel to go down 30 rows in the column D and bring back that 721 number. If you move this formula down a cell to D6 it would bring back what was in D36. You need to put a dollar sign in front of the 35 so your formula should read
Cell D5 = D$35. Now when you move the A's At Bats up or down in the sorted list it always pulls the value from row 35.

I've fixed all your formulas and have attached the workbook.

ALSO - you needed to remove the blank column (or part of it) between the team names and their stats. I've moved hitting and pitching team names to column B instead of A to acomplish this.

Thanks for your help! However, I still can't sort the "Leaders" data at the bottom of the spreadsheet. For example, I need to sort cells e5981 thru e6052 by highest to lowest values. Doing this still changes the data and doesn't sort even the changed data correctly (which seems really weird to me). I tried inserting the $ sign in the formulas for these cells before the sort, but that didn't help. Any ideas?

Also, did you remove the blank column in the top section (and move the team names over to column B) because it didn't look good or because that was necessary for the formulas to work?

Thanks again.

[MarvinP]

Hi,

The bottom of the spreadsheet has the same problem. in cell B5981 you have = E241 which is the Runs for player named Speaker. You need to change that formula to E$241. You will need to put the dollar sign in for each formula from E5981 down to E6202 to have your stats to come out right. You will need to do this for columns B, F, J and N's formulas.
ALSO - I see a blank column(s) between the playes names and their stat. Columns A and B are together so they will work together when you sort. BUT you have column E BLANK between columns D and F. That means when you try to sort Hits the names won't stay with the numbers.

For your second question. I did not delete column B to move the names next to the numbers in Rows 4 to 22. I simply moved the team names over a column, from A to B. The reason is that a completely blank row or column in a group of numbers keep them from being a "Table" and sorting correctly.

[MarvinP]

Hi somer,

We need to teach you Pivot Tables and this problem would be soooo easy. I'd make a table with only a few columns. It would have these columns:
Team, Player, Hit/Pitch, Series#, Stat, Number A Pivot Table would then give you all the information you wanted.

Look on the net for Pivot Table Tutorial and learn a little about them. If you convert some of your data to the above table format, I'll do the example pivot for you.

[somer]

As I mentioned, I already tried putting the $ in the formulas for the cells I need to sort, but that didn't help. The values still change when sorted.

I guess I'll look for a course on pivot tables as you suggest and start over. Thanks for your time.

[MarvinP]

Hi somer,

Did you look at the file I attached. I fixed all the dollar signs for the top two sections. Then you mentioned that the bottom sections also needed fixing. I didn't do that for you as it didn't look like you had seen or understood my top sections fix. On the top I needed to remove the blank column B by moving column A over to the right.

I'm positive the dollar sign (relative vs absolute cell referencing) is your problem. You could make yoiur design work if you took out the blank columns and put dollar signs in.

Study the link I gave you above and if you understand it and using tables (without blank rows or columns) it will solve your problem.

Sorry you didn't get what I was suggesting in my first few posts.

[MarvinP]

Hi somer,

Find the attached where I chopped your data onto a Stats TABLES sheet. I have created a Hitting Table and below it a Pitching table. Note - there can be no blank rows or columns in a table.

Then I've made a Hitting Pivot Table on one Sheet and a Pitching Pivot Table on the next. This is what I meant by doing pivot tables instead of your file format.

Pivot Tables have great abilities. You can select a single team or all the teams. You can sort by any of the statistic columns to compare players stats.

See if this might be a different (better?) way for you to deal with your data.

[somer]

I've gotten this worked out now (with leader board on separate sheet) and all seems to be working. Thanks again for your time and help!