MrExcel.com - Your One Stop for Excel Tips & Solutions

Want to sort the one column and the whole columns will be effected

Posted by pessona on June 22, 2000 2:02 AM

Hope somebody can help me. I want to sort a column and then the rest of the columns will be sorted accordingly.
FOr example, I have a table in a worksheet containing studentID, Marks for every Subject, Overall Marks and Percentage. The table looks like this..

StudentID Subejct1 Subject2 Subject3 Overall Percentage
1254 10 10 10 30 100%
1145 9 8 5 22 73%
5441 9 9 10 28 93%
and so on..
I want to sort the percentage so that student with highest percentage will be at the top and student with the lowest mark will be at the bottom(descending). This must also automatically sort the other columns. I've tried using the sort function in the Data menu, but it doesn't work. Does anybody has any idea how to do it?
TIA
-pessona-

Posted by DAvid on June 22, 0100 2:25 AM

Highlight Everything you want to sort then go up to the Data menu and select sort

Posted by pessona on June 22, 0100 2:36 AM

David,
Thanks for your reply.. I've done it.. but it didn't work. I am planning to do it using VBA, I want to create a command button that when we click on the button, it will automatically sort it descendingly. Any ideas?
Thanks

Posted by Jim on June 22, 0100 6:34 AM

The sort command will sort the list as a whole, by any column you choose, if there are no blank columns or rows anywhere in the list. If there are blank rows or columns, excel will interpret them as separate lists.

Posted by pessona on June 22, 0100 10:50 PM

Jim,
Thanks. I realized that you can't sort the column that has the formula for each cells. Is there any solution to it? Or if possible, can I use VBA to sort it automatically where I just use the command button to run it. Can somebody please help me?
TIA


Posted by david on June 22, 0100 11:29 PM

Try making a link to the cells you want to sort. Such as if in sheet 1 you have a range a1:f7
make a sheet 2 and make a1=sheet1 a1, f7 = sheet1 f7 for all the cells then you should be able to sort them. That way they are references not formula's.