simple automatic sorting

[Guest]

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! (e-mail address removed)

 

[Bob I]

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.

 

[Guest]

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.

 

[Bob I]

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?

 

[Gord Dibben]

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

 

[Guest]

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!

 

[Gord Dibben]

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

 

[Guest]

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.

 

[Gord Dibben]

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

 

[Guest]

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?

 

[Gord Dibben]

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

 

[RagDyer]

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.

 

[Edward Bradburn]

I'd just like to take the time to express my thanks for the code snippet Gord, it was just what I wanted.

Really appreciate the trouble people like you take here in helping Excel newcomers.

It's these little bits of help here and there that help people like myself go from viewing Excel as taking up valuable disk space to being a really useful application that can save time and money in any number of ways.

Your code is now being used to sort lists of tunes that I compile for myself and my band (I'm a keen amateur folk musician). I add the tune, it self-sorts and then I immediately have a filtered list of the tunes in dropdowns for input as data on another worksheet.

As another example, it is from replies such as yours that I have built an invoicing system for my one-man translation outfit here, thus saving me both the expense and hassle of using other invoicing systems (utterly overboard for a 1-person shop).

Thank you!

 

[Edward Bradburn]

I'd just like to take the time to express my thanks for the code snippet Gord, it was just what I wanted.

Really appreciate the trouble people like you take here in helping Excel newcomers.

It's these little bits of help here and there that help people like myself go from viewing Excel as taking up valuable disk space to being a really useful application that can save time and money in any number of ways.

Your code is now being used to sort lists of tunes that I compile for myself and my band (I'm a keen amateur folk musician). I add the tune, it self-sorts and then I immediately have a filtered list of the tunes in dropdowns for input as data on another worksheet.

As another example, it is from replies such as yours that I have built an invoicing system for my one-man translation outfit here, thus saving me both the expense and hassle of using other invoicing systems (utterly overboard for a 1-person shop).

Thank you!

 

[Gord Dibben]

Good to hear that a 3-year old posting is still out there helping people.

Thanks for the feedback.


Gord

 

[Stan Brown]

I'd just like to take the time to express my thanks for the code snippet Gord, it was just what I wanted.

Saying "thank you" is always polite, and I'm sure Gord appreciates
it.

I hate to jump on you, but why did you change the subject line and
start a new thread. If instead you had simply followed up on the
article you were thankful for, we would all have known what you were
talking about, and we might have been led to something cool.

This error seems pretty common all of a sudden. Have people
discovered some new rogue news host where threading is impossible and
every article needs a hand-typed subject line?

 

[Clif McIrvin]

Saying "thank you" is always polite, and I'm sure Gord appreciates
it.

I hate to jump on you, but why did you change the subject line and
start a new thread. If instead you had simply followed up on the
article you were thankful for, we would all have known what you were
talking about, and we might have been led to something cool.

This error seems pretty common all of a sudden. Have people
discovered some new rogue news host where threading is impossible and
every article needs a hand-typed subject line?

I wonder if this header line in the OP is a clue:

Message-ID: <[email protected]>

Anyone in this room familiar with eggheadcafe? I just visited there
trying to unravel why an OP doesn't seem to be seeing my replies .... at
first glance it seems that there is no respect for Subject Line at all
over there.

From the browsing I did, it seems that the Subject Line header on
replies is ignored and replaced by the first line(s) of the reply -- so
perhaps not something that the poster has any control over -- at the
least the browsing experience doesn't appear to give the visitor any
clues that threading is related to subject line, or vicea versa.

--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)

 

[Stan Brown]

[quoted text muted]

This error seems pretty common all of a sudden. Have people
discovered some new rogue news host where threading is impossible and
every article needs a hand-typed subject line?

I wonder if this header line in the OP is a clue:

Message-ID: <[email protected]>

Anyone in this room familiar with eggheadcafe? I just visited there
trying to unravel why an OP doesn't seem to be seeing my replies .... at
first glance it seems that there is no respect for Subject Line at all
over there.

Thanks for the detective work, Clif!

 

[Clif McIrvin]

[quoted text muted]

This error seems pretty common all of a sudden. Have people
discovered some new rogue news host where threading is impossible
and
every article needs a hand-typed subject line?

I wonder if this header line in the OP is a clue:

Message-ID: <[email protected]>

Anyone in this room familiar with eggheadcafe? I just visited there
trying to unravel why an OP doesn't seem to be seeing my replies ....
at
first glance it seems that there is no respect for Subject Line at
all
over there.

Thanks for the detective work, Clif!

You're welcome.

It appears that your speculation is right on target ... perhaps if
EggHeadCafe receives enough polite feedback from USENET regulars they
will modify their user interface -- they did respond to my emailed
inquiry.

--
Clif McIrvin

Change nomail.afraid.org to gmail.com to reply by email.
(nomail.afraid.org has been set up specifically for
use in usenet. Feel free to use it yourself.)