Excel List Sorting

[ben.lopresti]

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]

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

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

 

[raypayette]

Automatic sorting is not possible, however create a new macro that woul
sort your rows and specify a key such as Ctrl+e. Then after you add o
change data you simply press Ctrl+e

 

[ben.lopresti]

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

 

[ben.lopresti]

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]

Ben

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

My VBA skills are limited.


Gord

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 MS Excel MVP

 

[Dave Peterson]

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("D975")) 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.

 

[Gord Dibben]

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

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("D975")) 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.

Gord Dibben MS Excel MVP