 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007simple automatic sorting
Thanks for reading... I have a spreadsheet with a total of 7 columns, B through H. One of the columns, column F, contains dates. I want the spreadsheet to automatically sort itself by sorting column F, in an order which puts the oldest date towards the bottom and the most recent date at the top (the top row of my spreadsheet is row 5 - above that is the header row). The important thing is that the data in each row must stay "attached" with the cell containing the date. So I suppose I need to sort the entire sheet, not just the date column, by sorting the data found in the date column... gosh I hope that makes sense. :) Thanks! brianmsnyder@gmail.com Yes, select the entire sheet by clicking the box in the upper left between the row and column headers , and then Data, Sort, Column F, Ascending. > Thanks for reading... > [quoted text clipped - 7 lines] > by sorting the data found in the date column... gosh I hope that makes sense. > :) Thanks! brianmsnyder@gmail.com Thanks Bob. I'm fairly familiar with using the sorting tool, but that isn't exactly what I need here.. perhaps I didn't explain my question thoroughly enough. I need to have my sheet setup to automatically sort data every time I add new data to the sheet. So if I enter in something new, it will take that data and automatically put it where it needs to go based on the data in the date column. I don't want to have to use the sort tool every single time I put in fresh data. Make sense? This might be a programming issue. > Yes, select the entire sheet by clicking the box in the upper left > between the row and column headers , and then Data, Sort, Column F, [quoted text clipped - 11 lines] > > by sorting the data found in the date column... gosh I hope that makes sense. > > :) Thanks! brianmsnyder@gmail.com But do you really want it re-sorting itself every tim you make a cell entry?!?!? Might I suggest you record a macro, and then you may fire off the macro after you insert ALL the info? > Thanks Bob. I'm fairly familiar with using the sorting tool, but that isn't > exactly what I need here.. perhaps I didn't explain my question thoroughly [quoted text clipped - 19 lines] >>>by sorting the data found in the date column... gosh I hope that makes sense. >>> :) Thanks! brianmsnyder@gmail.com Brian See this google search result for a few methods. http://snipr.com/1n6gq But note my caveat about misspelled words if you go the event code route. Gord Dibben MS Excel MVP >Thanks Bob. I'm fairly familiar with using the sorting tool, but that isn't >exactly what I need here.. perhaps I didn't explain my question thoroughly [quoted text clipped - 19 lines] >> > by sorting the data found in the date column... gosh I hope that makes sense. >> > :) Thanks! brianmsnyder@gmail.com Gord - thanks for the info. I checked out the page and tried both the code and the formulas but I couldn't get either to work. I think my problem is that I know so little about code (and formulas that are this advanced) that I don't know how to edit the code/formulas to work specifically for my ranges of cells on my spreadsheet. I've uploaded the sheet I'm working on so that you or anyone else can download it and give this a try. Here's the link: http://brianmsnyder.googlepages.com/log.xls I want to be able to sort the entire sheet by due date, column F, and I want it to automatically resort itself every time I input new data. Hope this helps.. thanks! I downloaded and looked at your log.xls You have copied and pasted Sandy's Sub Macro2() into the wrong place. That code would go into a general module and be run from a button or shortcut key when you wanted to sort. The code I provided is to be pasted into the sheet module and will run automatically when you make a new entry. I do not know what your ranges are so can't speak to that. Decide which set of code you will use..........event or manual run. Post back with that decision and describe your ranges. Gord >Gord - thanks for the info. I checked out the page and tried both the code >and the formulas but I couldn't get either to work. I think my problem is [quoted text clipped - 8 lines] >it to automatically resort itself every time I input new data. Hope this >helps.. thanks! Thanks for the clarification. I'd like everything to happen automatically, without having to be run from a button or shortcut. In my mind, that defeats the purpose of this whole thing. If I was to run this from a button, I might as well just highlight my range and use the standard sort button that's already there. So to answer your questions, I'd like this to be event run, and the range I want it to apply to is B5:H100, sorting by column F. I hope that's all the info you need. Thanks. Adjusted code to be triggered when a date is entered in Column F which is column 6 in the code. Also set the range B4:H100 to accomodate your Titles in B4:H100 Note also the Order1:=xlAscending You can change that to Order1:=xlDescending if you choose. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column <> 6 Then Exit Sub On Error GoTo endit Application.EnableEvents = False Application.ScreenUpdating = False With Range("B4:F100") .Sort Key1:=Range("F4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With endit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Copy the code and right-click on the sheet tab. Paste into that sheet module. Gord >Thanks for the clarification. I'd like everything to happen automatically, >without having to be run from a button or shortcut. In my mind, that defeats [quoted text clipped - 5 lines] >want it to apply to is B5:H100, sorting by column F. I hope that's all the >info you need. Thanks. That did it! Thanks so much for the help. Just out of curiosity -- how is it that you, and so many others like you, both know so much about excel but also have enough time to help complete strangers with their problems? What's in it for you? What's in it for us? Nothing more than our desire to learn more about Excel. By helping others we glean knowledge that we probably wouldn't otherwise. Some of us are retired and other than golf have little to do if you don't count the Honey-do list<g> Participating in these news groups keeps my mind active and hopefully slows the synapses burn-out. Others have their reasons and you may get some more input. Gord >That did it! Thanks so much for the help. Just out of curiosity -- how is it >that you, and so many others like you, both know so much about excel but also >have enough time to help complete strangers with their problems? What's in it >for you? In January of 2000 I didn't know how to add 2 numbers in XL. I started out with a QUE "Special Edition" book on using Excel - Version 5.0 for Windows, and these News Groups. What I know today (not really that much) I gleaned from the folks here. So ... pay back time, with some *not* unselfish motives, since, as Gord said, you learn something new all the time, just from reading these groups. When you see the "back & forth" between responders, not even including the OP, there's a great deal of knowledge being transferred, even among the "experts". Plus, it's actually exactly like a hobby, it's fun and enjoyable, especially when the OPs include accolades in their feed-back.  SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > That did it! Thanks so much for the help. Just out of curiosity -- how is > it [quoted text clipped - 3 lines] > it > for you?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.