Just learning the tricks of the trade!

C

colbymack

Active Member

Joined

Jul 14, 2005

Messages

333

• Dec 6, 2005

• #2

If all of your columns are already contiguous (i.e. no blank columns between), you can just click on the Z->A button at the top of the screen with your cursor in the date field (don't highlight the column or it will think that is the only thing you want to sort - just click on one cell). This will resort the spreadsheet highest date to lowest. All rows will maintain their integrity (again, as long as you only have one cell selected).

HTH,
Colbymack

 

Upvote 0

J

JJRudd

New Member

Joined

Dec 6, 2005

Messages

34

• Dec 6, 2005

• #3

I may have mis-worded what I'm wanting. I know how to use the regular sort function. What I'm wanting is for it to automaticall sort as soon as the info in Column G changes. Using my example, if Bob completes a report today, I update column F, which would then update column G, and once I finish entering the date, I want it to automatically drop to the bottom of the list, as it's the most recently completed report.

 

Upvote 0

B

Banker1

Active Member

Joined

Mar 10, 2005

Messages

463

• Dec 6, 2005

• #4

You can also select your entire data range and go to Data --> Sort and follow the sort prompts for your data set.

 

Upvote 0

C

colbymack

Active Member

Joined

Jul 14, 2005

Messages

333

• Dec 7, 2005

• #5

Try searching the board for "worksheet_Selection_Change". The posts that it lists have a couple that sound familiar. There were only 23 hits when I performed this search this morning. Good luck!

Colbymack

 

Upvote 0

J

JJRudd

New Member

Joined

Dec 6, 2005

Messages

34

• Dec 7, 2005

• #6

Copy of Flight EPR Tracker.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Color Key:	Overdue	30 Days	90 Days
2
3
4
5	Name	Pos.	Supv Name	Supv Began	Start Date	Last Eval	Next Eval	Status Date	Date received	Supv Sus	VP Sus	Pres SuS	C/O Date
6	Bobby	admn	Dave	21-Nov-04	4-Jun-97	20-Nov-04	20-Nov-05			10-Nov-05	20-Nov-05	5-Dec-05	20-Nov-05
7	Jan	admn	Dave	2-Jan-05	3-Jul-01	20-Nov-04	20-Nov-05			20-Nov-05	4-Dec-05	18-Dec-05	1-Jan-06
8	Carl	admn	Mark	17-Mar-05	9-Apr-87	11-Dec-04	11-Dec-05
9	Frank	hp desk	Dave	16-Jan-05	27-May-03	11-Dec-04	11-Dec-05			4-Dec-05	18-Dec-05	1-Jan-05	15-Jan-05
10	Susan	hp desk	Arthur	1-Feb-05	3-Jun-03	15-Jan-05	15-Jan-06			4-Dec-05	18-Dec-05	1-Jan-05	15-Jan-05
11	Kyle	admin	Arthur	9-Feb-05	16-Oct-01	19-Jan-05	19-Jan-06		10-Nov-05	8-Dec-05	22-Dec-05	5-Jan-05	19-Jan-05
12	Billy	hp desk	Arthur	20-Jan-05	28-Aug-01	16-Mar-05	16-Mar-06		4-Nov-05	8-Dec-05	22-Dec-05	5-Jan-06	19-Jan-06
13	Jill	admn	Mark	29-Jan-05	28-May-03	16-Mar-05	16-Mar-06		10-Nov-05	17-Dec-05	31-Dec-05	14-Jan-05	28-Jan-05
14	Malcolm	admn	Mark	3-Feb-05	3-Jun-03	16-Mar-05	16-Mar-06		4-Nov-05	22-Dec-05	5-Jan-06	19-Jan-06	2-Feb-05
Report Tracker

Okay, This is what I have. I want it to automatically resort using the Proj. C/O Dt. Whenever It changes. Making the most recently completed drop to the bottom. For example, If Bobby completes his eval today, I will change the Last Eval to today, which will then auto change the next to a year from today and turn it white. I then want all of Bobby's info to drop to the bottom of the list because the Next report will be the farthest out.
I hope I'm not just running in circles with my questions, I'm just really pretty new to codes and functions.

 

Upvote 0

J

JJRudd

New Member

Joined

Dec 6, 2005

Messages

34

• Dec 7, 2005

• #7

Anybody have any ideas how I can do this?

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Dec 7, 2005

• #8

You can do that with Change event code.

See the VBA help topic Worksheet Object Events

One problem I can see is if you enter a typo and click out of the cell, whoops down to the bottom, or somewhere, you go ...

 

Upvote 0

C

colbymack

Active Member

Joined

Jul 14, 2005

Messages

333

• Dec 7, 2005

• #9

I will claim almost absolute ignorance on how this all works, but here is some code that I got to work. You would want to change the value of Range (A1:IV65536) to be the range of values that you want to have changed - this is selecting the entire worksheet.

HTH,
Colbymack

Code begins here:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG1 As Range


Set RNG1 = Range("A1:IV65536")
RNG1.Select
Selection.Sort Key1:=Range("H1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


End Sub

 

Upvote 0

C

colbymack

Active Member

Joined

Jul 14, 2005

Messages

333

• Dec 7, 2005

• #10

One more thing - in my post above, it was sorting on column H (indicated by the reference to H1). You will want to change that as well.

Colbymack

 

Upvote 0