Excel List Sorting

I am trying to keep my records in Excel, and I want to keep the list
sorted by "Last Date Contacted". This column is constantly being
updated, depending on when the person was contacted last.

Right now, I have to sort the list after each change to a cell in the
"Last Date Contacted" column. Is there any way to make the list
automatically sort itself in real-time, based on any changes that I
make to this column? (when I change the date, the row would
automatically jump to its proper place, based on the new date)

Thanks a lot.

-Ben LoPresti

Ben

You have to use event code in the sheet module.

Right-click on your sheet tab and "View Code"

Copy/paste this code into that module.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

As you enter data in column A it will sort.


Gord Dibben MS Excel MVP


On 5 Aug 2006 13:10:54 -0700, [email protected] wrote:

>I am trying to keep my records in Excel, and I want to keep the list
>sorted by "Last Date Contacted". This column is constantly being
>updated, depending on when the person was contacted last.
>
>Right now, I have to sort the list after each change to a cell in the
>"Last Date Contacted" column. Is there any way to make the list
>automatically sort itself in real-time, based on any changes that I
>make to this column? (when I change the date, the row would
>automatically jump to its proper place, based on the new date)
>
>Thanks a lot.
>
> -Ben LoPresti

Gord Dibben MS Excel MVP

Gord,
Thank you very much for your timely reply. It was very helpful. Here
is the problem that I am now experiencing.

Firstly, The column I need automatically sorted is column D (not A),
and I need it actually to start with row 9, and only go through row 75.
Is that at all possible?

Secondly, when I tried your code, it worked, but it would not link the
cells in column A with their respective cells in columns 2, 3, 4, etc.
The result was that the dates automatically jumped into a sorted
position, but they were not linked with the names, locations, etc. that
were to go with them in the other columns. The dates worked, but then
the other information was not.

Is there any way to have Excel sort my list ascending via the
information in D9-D75, and have it link the information in columns A-C
to the cells in their respective rows in column D?

I know I'm asking a lot - thank you very much.

-Ben LoPresti


Gord Dibben wrote:
> Ben
>
> You have to use event code in the sheet module.
>
> Right-click on your sheet tab and "View Code"
>
> Copy/paste this code into that module.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
> Header:=xlGuess, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
> End Sub
>
> As you enter data in column A it will sort.
>
>
> Gord Dibben MS Excel MVP
>
>
> On 5 Aug 2006 13:10:54 -0700, [email protected] wrote:
>
> >I am trying to keep my records in Excel, and I want to keep the list
> >sorted by "Last Date Contacted". This column is constantly being
> >updated, depending on when the person was contacted last.
> >
> >Right now, I have to sort the list after each change to a cell in the
> >"Last Date Contacted" column. Is there any way to make the list
> >automatically sort itself in real-time, based on any changes that I
> >make to this column? (when I change the date, the row would
> >automatically jump to its proper place, based on the new date)
> >
> >Thanks a lot.
> >
> > -Ben LoPresti
>
> Gord Dibben MS Excel MVP

Gord,
Thank you very much for your timely reply. It was very helpful. Here
is the problem that I am now experiencing.

Firstly, The column I need automatically sorted is column D (not A),
and I need it actually to start with row 9, and only go through row 75.
Is that at all possible?

Secondly, when I tried your code, it worked, but it would not link the
cells in column A with their respective cells in columns 2, 3, 4, etc.
The result was that the dates automatically jumped into a sorted
position, but they were not linked with the names, locations, etc. that
were to go with them in the other columns. The dates worked, but then
the other information was not.

Is there any way to have Excel sort my list ascending via the
information in D9-D75, and have it link the information in columns A-C
to the cells in their respective rows in column D?

I know I'm asking a lot - thank you very much.

-Ben LoPresti


Gord Dibben wrote:
> Ben
>
> You have to use event code in the sheet module.
>
> Right-click on your sheet tab and "View Code"
>
> Copy/paste this code into that module.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
> Header:=xlGuess, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
> End Sub
>
> As you enter data in column A it will sort.
>
>
> Gord Dibben MS Excel MVP
>
>
> On 5 Aug 2006 13:10:54 -0700, [email protected] wrote:
>
> >I am trying to keep my records in Excel, and I want to keep the list
> >sorted by "Last Date Contacted". This column is constantly being
> >updated, depending on when the person was contacted last.
> >
> >Right now, I have to sort the list after each change to a cell in the
> >"Last Date Contacted" column. Is there any way to make the list
> >automatically sort itself in real-time, based on any changes that I
> >make to this column? (when I change the date, the row would
> >automatically jump to its proper place, based on the new date)
> >
> >Thanks a lot.
> >
> > -Ben LoPresti
>
> Gord Dibben MS Excel MVP

Ben

You and I are going to have to wait for help on this.

My VBA skills are limited.


Gord

On 5 Aug 2006 15:27:50 -0700, [email protected] wrote:

>Gord,
>Thank you very much for your timely reply. It was very helpful. Here
>is the problem that I am now experiencing.
>
>Firstly, The column I need automatically sorted is column D (not A),
>and I need it actually to start with row 9, and only go through row 75.
> Is that at all possible?
>
>Secondly, when I tried your code, it worked, but it would not link the
>cells in column A with their respective cells in columns 2, 3, 4, etc.
>The result was that the dates automatically jumped into a sorted
>position, but they were not linked with the names, locations, etc. that
>were to go with them in the other columns. The dates worked, but then
>the other information was not.
>
>Is there any way to have Excel sort my list ascending via the
>information in D9-D75, and have it link the information in columns A-C
>to the cells in their respective rows in column D?
>
>I know I'm asking a lot - thank you very much.
>
>-Ben LoPresti
>
>
>Gord Dibben wrote:
>> Ben
>>
>> You have to use event code in the sheet module.
>>
>> Right-click on your sheet tab and "View Code"
>>
>> Copy/paste this code into that module.
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
>> Header:=xlGuess, OrderCustom:=1, _
>> MatchCase:=False, Orientation:=xlTopToBottom
>> End Sub
>>
>> As you enter data in column A it will sort.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>> On 5 Aug 2006 13:10:54 -0700, [email protected] wrote:
>>
>> >I am trying to keep my records in Excel, and I want to keep the list
>> >sorted by "Last Date Contacted". This column is constantly being
>> >updated, depending on when the person was contacted last.
>> >
>> >Right now, I have to sort the list after each change to a cell in the
>> >"Last Date Contacted" column. Is there any way to make the list
>> >automatically sort itself in real-time, based on any changes that I
>> >make to this column? (when I change the date, the row would
>> >automatically jump to its proper place, based on the new date)
>> >
>> >Thanks a lot.
>> >
>> > -Ben LoPresti
>>
>> Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

It would drive me nuts to have my data sorted as soon as I made a change. I'd
be more irritated if I made a typo and my data was sorted--and I couldn't find
my typo.

I'd rather use this technique (from Debra Dalgleish's site):
http://www.contextures.com/xlSort02.html

But you could modify Gord's routine if you really want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'only one cell at a time!
If Target.Cells.Count > 1 Then Exit Sub

'sort only if the change was in column D
If Intersect(Target, Me.Range("D9:D75")) Is Nothing Then Exit Sub

With Me.Range("A9:x75")
.Sort key1:=.Columns(4), order1:=xlAscending, _
key2:=.Columns(5), order2:=xlAscending, _
key3:=.Columns(1), order3:=xlAscending, _
header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

Change this portion:
With Me.Range("A9:x75")
to match the columns that include the range to sort (I stopped at column X).

This assumes that your data starts in row 9 (I used header:=xlno).

And I sorted by column 4 (D), then E, then A. Change/delete those if you don't
want them.

If that isn't sufficient, there are other ways, too.



[email protected] wrote:
>
> Gord,
> Thank you very much for your timely reply. It was very helpful. Here
> is the problem that I am now experiencing.
>
> Firstly, The column I need automatically sorted is column D (not A),
> and I need it actually to start with row 9, and only go through row 75.
> Is that at all possible?
>
> Secondly, when I tried your code, it worked, but it would not link the
> cells in column A with their respective cells in columns 2, 3, 4, etc.
> The result was that the dates automatically jumped into a sorted
> position, but they were not linked with the names, locations, etc. that
> were to go with them in the other columns. The dates worked, but then
> the other information was not.
>
> Is there any way to have Excel sort my list ascending via the
> information in D9-D75, and have it link the information in columns A-C
> to the cells in their respective rows in column D?
>
> I know I'm asking a lot - thank you very much.
>
> -Ben LoPresti
>
> Gord Dibben wrote:
> > Ben
> >
> > You have to use event code in the sheet module.
> >
> > Right-click on your sheet tab and "View Code"
> >
> > Copy/paste this code into that module.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
> > Header:=xlGuess, OrderCustom:=1, _
> > MatchCase:=False, Orientation:=xlTopToBottom
> > End Sub
> >
> > As you enter data in column A it will sort.
> >
> >
> > Gord Dibben MS Excel MVP
> >
> >
> > On 5 Aug 2006 13:10:54 -0700, [email protected] wrote:
> >
> > >I am trying to keep my records in Excel, and I want to keep the list
> > >sorted by "Last Date Contacted". This column is constantly being
> > >updated, depending on when the person was contacted last.
> > >
> > >Right now, I have to sort the list after each change to a cell in the
> > >"Last Date Contacted" column. Is there any way to make the list
> > >automatically sort itself in real-time, based on any changes that I
> > >make to this column? (when I change the date, the row would
> > >automatically jump to its proper place, based on the new date)
> > >
> > >Thanks a lot.
> > >
> > > -Ben LoPresti
> >
> > Gord Dibben MS Excel MVP

--

Dave Peterson

Thanks for jumping in Dave.

Good point about the typo and not finding it.

"Gord's routine" began life as code from Debra's DataValListAddSort.xls


Gord

On Sat, 05 Aug 2006 18:52:02 -0500, Dave Peterson <[email protected]>
wrote:

>It would drive me nuts to have my data sorted as soon as I made a change. I'd
>be more irritated if I made a typo and my data was sorted--and I couldn't find
>my typo.
>
>I'd rather use this technique (from Debra Dalgleish's site):
>http://www.contextures.com/xlSort02.html
>
>But you could modify Gord's routine if you really want:
>
>Option Explicit
>Private Sub Worksheet_Change(ByVal Target As Range)
>
> 'only one cell at a time!
> If Target.Cells.Count > 1 Then Exit Sub
>
> 'sort only if the change was in column D
> If Intersect(Target, Me.Range("D9:D75")) Is Nothing Then Exit Sub
>
> With Me.Range("A9:x75")
> .Sort key1:=.Columns(4), order1:=xlAscending, _
> key2:=.Columns(5), order2:=xlAscending, _
> key3:=.Columns(1), order3:=xlAscending, _
> header:=xlNo, OrderCustom:=1, _
> MatchCase:=False, Orientation:=xlTopToBottom
> End With
>End Sub
>
>Change this portion:
>With Me.Range("A9:x75")
>to match the columns that include the range to sort (I stopped at column X).
>
>This assumes that your data starts in row 9 (I used header:=xlno).
>
>And I sorted by column 4 (D), then E, then A. Change/delete those if you don't
>want them.
>
>If that isn't sufficient, there are other ways, too.
>
>
>
>[email protected] wrote:
>>
>> Gord,
>> Thank you very much for your timely reply. It was very helpful. Here
>> is the problem that I am now experiencing.
>>
>> Firstly, The column I need automatically sorted is column D (not A),
>> and I need it actually to start with row 9, and only go through row 75.
>> Is that at all possible?
>>
>> Secondly, when I tried your code, it worked, but it would not link the
>> cells in column A with their respective cells in columns 2, 3, 4, etc.
>> The result was that the dates automatically jumped into a sorted
>> position, but they were not linked with the names, locations, etc. that
>> were to go with them in the other columns. The dates worked, but then
>> the other information was not.
>>
>> Is there any way to have Excel sort my list ascending via the
>> information in D9-D75, and have it link the information in columns A-C
>> to the cells in their respective rows in column D?
>>
>> I know I'm asking a lot - thank you very much.
>>
>> -Ben LoPresti
>>
>> Gord Dibben wrote:
>> > Ben
>> >
>> > You have to use event code in the sheet module.
>> >
>> > Right-click on your sheet tab and "View Code"
>> >
>> > Copy/paste this code into that module.
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending, _
>> > Header:=xlGuess, OrderCustom:=1, _
>> > MatchCase:=False, Orientation:=xlTopToBottom
>> > End Sub
>> >
>> > As you enter data in column A it will sort.
>> >
>> >
>> > Gord Dibben MS Excel MVP
>> >
>> >
>> > On 5 Aug 2006 13:10:54 -0700, [email protected] wrote:
>> >
>> > >I am trying to keep my records in Excel, and I want to keep the list
>> > >sorted by "Last Date Contacted". This column is constantly being
>> > >updated, depending on when the person was contacted last.
>> > >
>> > >Right now, I have to sort the list after each change to a cell in the
>> > >"Last Date Contacted" column. Is there any way to make the list
>> > >automatically sort itself in real-time, based on any changes that I
>> > >make to this column? (when I change the date, the row would
>> > >automatically jump to its proper place, based on the new date)
>> > >
>> > >Thanks a lot.
>> > >
>> > > -Ben LoPresti
>> >
>> > Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP