Need help with Excel 2007 - table not sorting numbers correctly

[someguy4321]

Hi,

I'm using Excel 2007 and I've created a table and want to sort the numbers, highest to lowest. Since the values are got from another part of the spreadsheet, I've had to use commands like =sum(cell + cell etc). I can't sort these number values and I tried to change the values to real numbers, etc but nothing. This only worked when I created another table and copied all the values in the table, and it worked. Please find my excel spreadsheet and try to fix it. The problem is shown half-way down the spreadsheet and I've 'painted' the area red to highlight the troubled-area.

Thanks

Simon

[FDibbins]

Im not really sure what you are trying to do, but I will say that I suggest you try and restructure your data. Excel works best if all the data is in a straght-forward table with 1 set of headings across the top, and allteh data falling (sequentially - although not necessarily) under the headings

[someguy4321]

Yes, I know, it's a mess at the moment. It's in it's 'infancy'.

Could you sort my table? I highlighted it red.

[newdoverman]

I don't know if there are other problems on the worksheet but, I found some merged cells that prevented sorting so I unmerged them and deleted the empty cells. Another thing that I found was formulae written like this:

Please Login or Register  to view this content.

These are just made up references to show the type of thing I'm talking about. This could be written:

Please Login or Register  to view this content.

OR

Please Login or Register  to view this content.

Anyway, here is your file that can have the red area sorted.

[someguy4321]

Thanks heaps, I'll check it out asap.

[someguy4321]

newdoverman, I just checked it, the red area isn't sorting....

[FDibbins]

not with your data like it is, no

Here is a start to normalising your table. I used some very basic forumlas to sort your data for you. If this is something you can work with, I will see what we can do about working onthe rest of your summary

(Bokke rule!!! )

[newdoverman]

The cell references in the RED area have to be ABSOLUTE because sorting changes the cell references and they become invalid for the records that they are attached to.

[someguy4321]

Thanks for your help newdoverman, I have fixed 2 columns thanks to you. However, 2 columns are still not working, even though I've used ABSOLUTE commands. They're the 'Average Points scored at home' and 'Average Points scored away' columns. Both these columns don't sort the numbers from largest to smallest properly.

[newdoverman]

Check out the cell references for the teams that should be calculated against what is actually in the cells in the RED area. In many cases, they just don't match. The wrong references are being applied to teams and therefore incorrect values are being used in the calculations.

Eg: Team Auckland's red area is tied to team Manawatu.
Wellington is tied to Canterbury

This is 2 out of the first 3 teams that I checked.

All of the references will have to be verified before this has a chance to work.

[newdoverman]

I combined all your little tables into one table on sheet3. See how this suits your needs.

[FDibbins]

I take it my consolidated table was not what you were looking for?

[someguy4321]

Thanks newdoverman, the table looks GREAT!!! I was thinking of a way to condense all my data and you did it perfectly.

I checked out the cell references in my original spreadsheet and I found one error in the absolute references, which I fixed. All the references look correct now (in the red zone) but the last 2 columns still don't sort. Please find my latest updated spreadsheet attached...

[newdoverman]

I tried many times to get the last two columns to sort properly and failed miserably. I even started out by having all the tables in order of the teams and then checked that the absolute references were pointing to the correct cells using the auditing using the Trace Precedences. I got all kinds of strange results after a sort. The cell references were pointing to ridiculous locations after sorting....I just gave up and produced the table on sheet3 for you. That one sorts perfectly.

[FDibbins]

My table sorts just fine - I think, hard to tell with no feedback