Coach1310 Posted September 24, 2009 Share Posted September 24, 2009 I am trying to create a table in Excel where I use the values listed in one of the columns to keep it sorted. These values will need to be updated regularily and I would like for the table to be resorted automatically. Anybody able to help me with this?Thanks Quote Link to comment Share on other sites More sharing options...
Dave S Posted September 24, 2009 Share Posted September 24, 2009 you can select the column (click on the letter in the header of your column) you want to sort by and then click on the sort key in your menu bar. it will give you a couple options to sort from. I have MS Excel '07, so unless you have the same I can't give you more defined instructions unless I was at my work computer.I think the key itself is the same though. If so it's the AZ (A over Z) with a down arrow next to it. Hope this helps. Quote Link to comment Share on other sites More sharing options...
Coach1310 Posted September 24, 2009 Author Share Posted September 24, 2009 Okay.... I have Excel '03 and the instructions you gave me worked to sort the column, but how do I get it to move the all the other parts of that row with it....EXAMPLEIf I have the following each in their own colums, how do I get it to sort the entire table due to the value of a certain columnMonth # of sales % increase revenue profitJanuary 27 11 $5000 $285 February 19 09 $3200 $134etc....I think you get the idea. How do I get it to sort by the revenue column or # of sales column AND take the other values of that row with it?Thanks again Quote Link to comment Share on other sites More sharing options...
Dave S Posted September 24, 2009 Share Posted September 24, 2009 Anytime I've tried to sort in MS Excel '03, there was another window that opened and had something like 3 options to sort by. Only choose the top and leave the others blank.Let me see if I can dig into my work e-mail account and find a file that I worked on and open to find out for sure. Quote Link to comment Share on other sites More sharing options...
BobT Posted September 24, 2009 Share Posted September 24, 2009 I don't have Excel '03 here at home but if you look a little closer in the sort menu options you should be able to find a means to sort with filtering. Select the columns you want to include and then open the sort filter and define which column you want to use as your sort basis. Quote Link to comment Share on other sites More sharing options...
Dave S Posted September 24, 2009 Share Posted September 24, 2009 When you want to sort and the window opens up, select "Expand the selection".This should sort everthing and keep your rows intact, but sort the rows in order based on the column you choose. Quote Link to comment Share on other sites More sharing options...
Jim Jensen Posted September 24, 2009 Share Posted September 24, 2009 You can do this with a macro. The macro would look like this.Private Sub Worksheet_Calculate()Range("A1:B9").Sort _Key1:=Range("B2"), _Order1:=xlAscending, _Key2:=Range("A2"), _Order2:=xlAscending, _Header:=xlGuess, _OrderCustom:=1, _MatchCase:=False, _Orientation:=xlTopToBottom, _DataOption1:=xlSortNormal, _DataOption2:=xlSortNormalEnd Sub Quote Link to comment Share on other sites More sharing options...
LMITOUT Posted September 24, 2009 Share Posted September 24, 2009 Okay.... I have Excel '03 and the instructions you gave me worked to sort the column, but how do I get it to move the all the other parts of that row with it....EXAMPLEIf I have the following each in their own colums, how do I get it to sort the entire table due to the value of a certain columnMonth # of sales % increase revenue profitJanuary 27 11 $5000 $285 February 19 09 $3200 $134etc....I think you get the idea. How do I get it to sort by the revenue column or # of sales column AND take the other values of that row with it?Thanks again Highlight the all the columns that have the data you want sorted. If you have data in columns B, C, and D, click on the B column at the top and highlight C and D with it by holding down the left mouse button on B and dragging across C and D. Or, just highlight all the cells that have the data you wish to sort.Go to 'Data' on the toolbar and then click on 'sort' in the drop down menu.In the window that opens select which column you wish to sort by and if you want it ascending or descending. Quote Link to comment Share on other sites More sharing options...
Coach1310 Posted September 24, 2009 Author Share Posted September 24, 2009 Thanks for the info guys, but I have tried all of your suggestions on my work computer Excel 03 and my home computer which is excel '07 on Vista. I still haven't been able to get it to work.... not sure where to go from here. I appreciate all the help... Thanks again. Quote Link to comment Share on other sites More sharing options...
Chef Posted September 24, 2009 Share Posted September 24, 2009 You might want to take this to MS Help:http://www.microsoft.com/atwork Quote Link to comment Share on other sites More sharing options...
Whoaru99 Posted September 24, 2009 Share Posted September 24, 2009 Thanks for the info guys, but I have tried all of your suggestions on my work computer Excel 03 and my home computer which is excel '07 on Vista. I still haven't been able to get it to work.... not sure where to go from here. I appreciate all the help... Thanks again. Maybe it would help if you describe to us exactly the process you are trying to use?I have both Excel 03 and 07 and the sort works as others have described.If you simply want to resort the whole table, just click the square box in the upper left corner of the sheet (just to the left of column A, and just above the row numbers). This should cause the whole sheet to be selected or "highlighted", if you will. Then click the "Sort" button and a dialog box opens asking how you want to sort the data. Quote Link to comment Share on other sites More sharing options...
upnorth Posted September 24, 2009 Share Posted September 24, 2009 In upper right hand corner of the spreadsheet there is a small cell that is the select whole worksheet. Press that and then go to data and sort and pick the column you want to sort by. That will sort your spreadhsheet by that column. If you want to sort automatically as you edit it.... If found this on the web. Basically you need to create VB script. Quote:There shouldn't be a macro to run. Dave's code runs on the WorkSheet_Change event. i.e. this code is run whenever a cell is change. You would need to tweak it to only run when data is entered into column "G" e.g.:Private Sub Worksheet_Change(ByVal Target As Excel.Range)'Don't run this code unless a value is last entered in column "G" If Not Target.Column = "7" Then Exit Sub 'Prevents endless loops Application.EnableEvents = False 'Assumptions '1. Data only housed in Columns A:G '2. The first row contains headings or labels '3 Column "G" is used as the sort criteria Me.UsedRange.Sort Key1:=Columns("G"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Application.EnableEvents = TrueEnd SubNow, to use this code:1. Right click on the name tab of the worksheet that will use house the data2. Select "View Code"3. Copy and paste the above code into the worksheet module that appears4. Hit ALT+F11 to return to the spreadsheet5. Type away merrily safe in the knowledge that your data is getting sorted based on teh order of values in column "G".NOTE:: Check my assumptions in the comments and change the code to reflect what you want. Quote Link to comment Share on other sites More sharing options...
Mike Wallace Posted September 25, 2009 Share Posted September 25, 2009 Highlight it all, and insert a list.Okay/next all of the pop up box questions. It will box in all of your data.Go to the column you want to sort by and click the up/down triangles on the column header. It will let you then sort by what you want and the rows will follow accordingly.I think this is what you are asking for. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now ↓↓↓ or ask your question and then register. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.