Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    14

    Unanswered: Automatic sort by colum

    How can I get a worksheet to sort automatically by a certain column? When I add a new row, one of the columns will be a date. I would like the sheet to automatically sort my new entry in the correct spot.

    Also, anyone know of any good (beginner) tutorials for Excel online? I know Excel can do a lot but am having difficulty figuring it out.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Yes, it is possible to do that, by VBA code. If you can wait a few minutes.

    regarding training, there are several good web sites.

    http://www.ozgrid.com has many training plans, free Tips and tricks (very helpful), and an excellent forum.

    http://www.contextures.com/tiptech.html

    The following is a good thought provoking paper about spreadsheet design. Well worth the time.

    http://www.mang.canterbury.ac.nz/peo.../spreadsheets/
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, this involves VBA code. This assumes four columns, and the Date is in Column B, with actual dates beginning in B2.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 2 Then 'if column B gets changed
        With Range("A1:D" & Range("D65536").End(xlUp).Row)
            .Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        End With
        End If
    End Sub
    To place this code, right-click on the worksheet tab (bottom of the worksheet), then choose "View Code". This will open the Visual Basic Editor (VBE) window. Then paste this into that window.

    Then go back to your sheet and begin entering data. Make sure that the Date column is formatted as date (choose whatever Date format you want).

    [EDIT: Corrected the column number and column reference in second line of code.]
    Last edited by shades; 09-17-04 at 16:54.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by juststo
    I know Excel can do a lot but am having difficulty figuring it out.
    Best way is to ask questions and experiment, then ask more questions and experiment some more. There are many who will take the time to walk you through the appropriate steps.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Sep 2004
    Posts
    14
    Thanks for the advice and code. It works, but it doesn't update if I modify a date. Any ideas???

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    In the second line change the 4 to a 2.

    Code:
    If Target.Column = 2 Then 'if column B gets changed
    Last edited by shades; 09-17-04 at 16:47.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •