A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

automatic data sorting?



 
 
Thread Tools Display Modes
  #1  
Old December 14th, 2007, 05:32 PM posted to microsoft.public.excel.worksheet.functions
Patrick
external usenet poster
 
Posts: 412
Default automatic data sorting?

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.
  #2  
Old December 14th, 2007, 06:03 PM posted to microsoft.public.excel.worksheet.functions
ryguy7272
external usenet poster
 
Posts: 1,593
Default automatic data sorting?

This is an auto-sort issue, which is easily remedied with this macro:
Right-click on the sheet that you want to auto-sort data on and then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and the sort
starts in row 2. You will, almost certainly, need to make a few small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.

  #3  
Old December 14th, 2007, 06:19 PM posted to microsoft.public.excel.worksheet.functions
Patrick
external usenet poster
 
Posts: 412
Default automatic data sorting?

Thank you. I am not at all savy or familiar with macros but it appears to be
time I learned. I wil give this a shot and get back to you!

"ryguy7272" wrote:

This is an auto-sort issue, which is easily remedied with this macro:
Right-click on the sheet that you want to auto-sort data on and then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and the sort
starts in row 2. You will, almost certainly, need to make a few small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.

  #4  
Old December 14th, 2007, 07:11 PM posted to microsoft.public.excel.worksheet.functions
Patrick
external usenet poster
 
Posts: 412
Default automatic data sorting?

RYGUY! I right click on the sheet but all I get is a drop down menu. No
window to post any code. What am I missing?

??????????

"ryguy7272" wrote:

This is an auto-sort issue, which is easily remedied with this macro:
Right-click on the sheet that you want to auto-sort data on and then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and the sort
starts in row 2. You will, almost certainly, need to make a few small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.

  #5  
Old December 14th, 2007, 08:43 PM posted to microsoft.public.excel.worksheet.functions
ryguy7272
external usenet poster
 
Posts: 1,593
Default automatic data sorting?

Right-click on the tab and select 'View Code'. Paste the code I gave you
right in there...



--
RyGuy


"Patrick" wrote:

RYGUY! I right click on the sheet but all I get is a drop down menu. No
window to post any code. What am I missing?

??????????

"ryguy7272" wrote:

This is an auto-sort issue, which is easily remedied with this macro:
Right-click on the sheet that you want to auto-sort data on and then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and the sort
starts in row 2. You will, almost certainly, need to make a few small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.

  #6  
Old December 14th, 2007, 09:21 PM posted to microsoft.public.excel.worksheet.functions
Patrick
external usenet poster
 
Posts: 412
Default automatic data sorting?

Thanks again. I did that and must be missing something. Let me tell you
what I had done before initally posting.

I used the macro recorder and set a hot key. Now, I can enter all kinds of
different dates, hit control s and it sorts everyting for me. The missing
component is to get this to happen without me having to use control s. I
would like it to happen as soon as it is type in. I am guessing that
something in the formula you sent does that but I can't figure out what.

Anyway, I did right click on the tab and pasted your stuff. I changed the
range to reflect A8 because that is the first cell that needs to be
addressed. I then saved the workbook. I opened it again, typed an earlier
date at the end of the column and hoped it would aytomatically be sorted. It
didn't happen and I am sure I am missing something I was supposed to have
done.

I was wondering if I could edit the macro that I "recorded" with the macro
recorder that would tell it to run automatically without using the hot key.
No rush getting back to me and I do appreciate all of your help.
"ryguy7272" wrote:

Right-click on the tab and select 'View Code'. Paste the code I gave you
right in there...



--
RyGuy


"Patrick" wrote:

RYGUY! I right click on the sheet but all I get is a drop down menu. No
window to post any code. What am I missing?

??????????

"ryguy7272" wrote:

This is an auto-sort issue, which is easily remedied with this macro:
Right-click on the sheet that you want to auto-sort data on and then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and the sort
starts in row 2. You will, almost certainly, need to make a few small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.

  #7  
Old December 14th, 2007, 10:11 PM posted to microsoft.public.excel.worksheet.functions
Trying
external usenet poster
 
Posts: 49
Default automatic data sorting?

Please know that I am here mostly to observe and learn. I have very little to
contribute, if any, by way of solutions to problems posted here.

From my limited experience with Excel, and even less with macros, I would
hazard a guess that what you are wanting is something like having a
motionsensor controlled room light that turns on without you having to so
much as flick a switch. And I would liken your pressing Ctrl + s as switching
on the room light by flicking a switch. That is if I understand what you want
done as Excel automatically sorting your list as soon as you input the
previously forgotten entry without you having to do anything else.

"Patrick" wrote:

Thanks again. I did that and must be missing something. Let me tell you
what I had done before initally posting.

I used the macro recorder and set a hot key. Now, I can enter all kinds of
different dates, hit control s and it sorts everyting for me. The missing
component is to get this to happen without me having to use control s. I
would like it to happen as soon as it is type in. I am guessing that
something in the formula you sent does that but I can't figure out what.

Anyway, I did right click on the tab and pasted your stuff. I changed the
range to reflect A8 because that is the first cell that needs to be
addressed. I then saved the workbook. I opened it again, typed an earlier
date at the end of the column and hoped it would aytomatically be sorted. It
didn't happen and I am sure I am missing something I was supposed to have
done.

I was wondering if I could edit the macro that I "recorded" with the macro
recorder that would tell it to run automatically without using the hot key.
No rush getting back to me and I do appreciate all of your help.
"ryguy7272" wrote:

Right-click on the tab and select 'View Code'. Paste the code I gave you
right in there...



--
RyGuy


"Patrick" wrote:

RYGUY! I right click on the sheet but all I get is a drop down menu. No
window to post any code. What am I missing?

??????????

"ryguy7272" wrote:

This is an auto-sort issue, which is easily remedied with this macro:
Right-click on the sheet that you want to auto-sort data on and then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and the sort
starts in row 2. You will, almost certainly, need to make a few small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.

  #8  
Old December 14th, 2007, 10:39 PM posted to microsoft.public.excel.worksheet.functions
Patrick
external usenet poster
 
Posts: 412
Default automatic data sorting?

Good analogy. That is exactly what I was hoping for although I can certainly
live with the macro I have been able to create.

"TRYING" wrote:

Please know that I am here mostly to observe and learn. I have very little to
contribute, if any, by way of solutions to problems posted here.

From my limited experience with Excel, and even less with macros, I would
hazard a guess that what you are wanting is something like having a
motionsensor controlled room light that turns on without you having to so
much as flick a switch. And I would liken your pressing Ctrl + s as switching
on the room light by flicking a switch. That is if I understand what you want
done as Excel automatically sorting your list as soon as you input the
previously forgotten entry without you having to do anything else.

"Patrick" wrote:

Thanks again. I did that and must be missing something. Let me tell you
what I had done before initally posting.

I used the macro recorder and set a hot key. Now, I can enter all kinds of
different dates, hit control s and it sorts everyting for me. The missing
component is to get this to happen without me having to use control s. I
would like it to happen as soon as it is type in. I am guessing that
something in the formula you sent does that but I can't figure out what.

Anyway, I did right click on the tab and pasted your stuff. I changed the
range to reflect A8 because that is the first cell that needs to be
addressed. I then saved the workbook. I opened it again, typed an earlier
date at the end of the column and hoped it would aytomatically be sorted. It
didn't happen and I am sure I am missing something I was supposed to have
done.

I was wondering if I could edit the macro that I "recorded" with the macro
recorder that would tell it to run automatically without using the hot key.
No rush getting back to me and I do appreciate all of your help.
"ryguy7272" wrote:

Right-click on the tab and select 'View Code'. Paste the code I gave you
right in there...



--
RyGuy


"Patrick" wrote:

RYGUY! I right click on the sheet but all I get is a drop down menu. No
window to post any code. What am I missing?

??????????

"ryguy7272" wrote:

This is an auto-sort issue, which is easily remedied with this macro:
Right-click on the sheet that you want to auto-sort data on and then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and the sort
starts in row 2. You will, almost certainly, need to make a few small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date. Occasionally, I have to
add an entry that I overlooked for a previous date. Then I usually highlight
everything and have Excel sort to put things in the right order. My question
is whether I can tell Excel that if I enter a date that is earlier in the
column than a previous date it should automatically sort and put things in
order for me.

  #9  
Old December 15th, 2007, 02:14 AM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default automatic data sorting?

Your dates can be easily "auto" sorted in another column using formulas.
The question is, how many other columns (fields) are you looking to sort
along with the dates, and ... are there duplicate dates?

The formulas could be in the actual "presentation" cells, while your data
entry itself, is in an out-of-the-way location.

Post back if you might be interested in pursuing this type of procedure.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Patrick" wrote in message
news
Good analogy. That is exactly what I was hoping for although I can
certainly
live with the macro I have been able to create.

"TRYING" wrote:

Please know that I am here mostly to observe and learn. I have very
little to
contribute, if any, by way of solutions to problems posted here.

From my limited experience with Excel, and even less with macros, I would
hazard a guess that what you are wanting is something like having a
motionsensor controlled room light that turns on without you having to so
much as flick a switch. And I would liken your pressing Ctrl + s as
switching
on the room light by flicking a switch. That is if I understand what you
want
done as Excel automatically sorting your list as soon as you input the
previously forgotten entry without you having to do anything else.

"Patrick" wrote:

Thanks again. I did that and must be missing something. Let me tell
you
what I had done before initally posting.

I used the macro recorder and set a hot key. Now, I can enter all
kinds of
different dates, hit control s and it sorts everyting for me. The
missing
component is to get this to happen without me having to use control s.
I
would like it to happen as soon as it is type in. I am guessing that
something in the formula you sent does that but I can't figure out
what.

Anyway, I did right click on the tab and pasted your stuff. I changed
the
range to reflect A8 because that is the first cell that needs to be
addressed. I then saved the workbook. I opened it again, typed an
earlier
date at the end of the column and hoped it would aytomatically be
sorted. It
didn't happen and I am sure I am missing something I was supposed to
have
done.

I was wondering if I could edit the macro that I "recorded" with the
macro
recorder that would tell it to run automatically without using the hot
key.
No rush getting back to me and I do appreciate all of your help.
"ryguy7272" wrote:

Right-click on the tab and select 'View Code'. Paste the code I gave
you
right in there...



--
RyGuy


"Patrick" wrote:

RYGUY! I right click on the sheet but all I get is a drop down
menu. No
window to post any code. What am I missing?

??????????

"ryguy7272" wrote:

This is an auto-sort issue, which is easily remedied with this
macro:
Right-click on the sheet that you want to auto-sort data on and
then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and
the sort
starts in row 2. You will, almost certainly, need to make a few
small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date.
Occasionally, I have to
add an entry that I overlooked for a previous date. Then I
usually highlight
everything and have Excel sort to put things in the right
order. My question
is whether I can tell Excel that if I enter a date that is
earlier in the
column than a previous date it should automatically sort and
put things in
order for me.



  #10  
Old February 20th, 2008, 04:20 PM posted to microsoft.public.excel.worksheet.functions
bkunes
external usenet poster
 
Posts: 33
Default automatic data sorting?

I WOULD BE INTERESTED IN THIS OPTION WITH 8 COLUMNS

--



"RagDyer" wrote:

Your dates can be easily "auto" sorted in another column using formulas.
The question is, how many other columns (fields) are you looking to sort
along with the dates, and ... are there duplicate dates?

The formulas could be in the actual "presentation" cells, while your data
entry itself, is in an out-of-the-way location.

Post back if you might be interested in pursuing this type of procedure.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Patrick" wrote in message
news
Good analogy. That is exactly what I was hoping for although I can
certainly
live with the macro I have been able to create.

"TRYING" wrote:

Please know that I am here mostly to observe and learn. I have very
little to
contribute, if any, by way of solutions to problems posted here.

From my limited experience with Excel, and even less with macros, I would
hazard a guess that what you are wanting is something like having a
motionsensor controlled room light that turns on without you having to so
much as flick a switch. And I would liken your pressing Ctrl + s as
switching
on the room light by flicking a switch. That is if I understand what you
want
done as Excel automatically sorting your list as soon as you input the
previously forgotten entry without you having to do anything else.

"Patrick" wrote:

Thanks again. I did that and must be missing something. Let me tell
you
what I had done before initally posting.

I used the macro recorder and set a hot key. Now, I can enter all
kinds of
different dates, hit control s and it sorts everyting for me. The
missing
component is to get this to happen without me having to use control s.
I
would like it to happen as soon as it is type in. I am guessing that
something in the formula you sent does that but I can't figure out
what.

Anyway, I did right click on the tab and pasted your stuff. I changed
the
range to reflect A8 because that is the first cell that needs to be
addressed. I then saved the workbook. I opened it again, typed an
earlier
date at the end of the column and hoped it would aytomatically be
sorted. It
didn't happen and I am sure I am missing something I was supposed to
have
done.

I was wondering if I could edit the macro that I "recorded" with the
macro
recorder that would tell it to run automatically without using the hot
key.
No rush getting back to me and I do appreciate all of your help.
"ryguy7272" wrote:

Right-click on the tab and select 'View Code'. Paste the code I gave
you
right in there...



--
RyGuy


"Patrick" wrote:

RYGUY! I right click on the sheet but all I get is a drop down
menu. No
window to post any code. What am I missing?

??????????

"ryguy7272" wrote:

This is an auto-sort issue, which is easily remedied with this
macro:
Right-click on the sheet that you want to auto-sort data on and
then paste
this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long

If Target.Column 2 Then Exit Sub

Application.ScreenUpdating = False

EndData = Cells(Rows.Count, 1).End(xlUp).Row

With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End With

Application.ScreenUpdating = False
End Sub

Notice, you are going to sort based on the values in Colun B, and
the sort
starts in row 2. You will, almost certainly, need to make a few
small
modifications to the code; change this:
Range("B2")
to suit your needs.


Regards,
Ryan---

--
RyGuy


"Patrick" wrote:

I have a spreadsheet where entries are made by date.
Occasionally, I have to
add an entry that I overlooked for a previous date. Then I
usually highlight
everything and have Excel sort to put things in the right
order. My question
is whether I can tell Excel that if I enter a date that is
earlier in the
column than a previous date it should automatically sort and
put things in
order for me.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.