| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alphabetize when data changes
Hello,
I have a workbook that has a number of pages. Each page contains a list of roughly 30 students. I want to have one master sheet that lists all the students in alphabetical order. Right now I have a formula that pulls the names from each name slot on each of the pages as well as a number showing which page. However, I would like it to automatically realphabetize whenever I add an additional name. I dont know if this is possible, but it would really help because the teacher I am doing it for is fairly illiterate when it comes to computers and would probably mess it up. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alphabetize when data changes
Try this, it re-alphabetises from cell A1 when a new entry is added, paste this code directly on to the code sheet for sheet you want this to happen on. Regards Simon Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Column = 2 Then 'This tells the sub not to work if the column is number 2 or more Exit Sub ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if column 1 Columns("A:A").Select 'This is your column range and the A1 below is telling the sort where to start Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=499263 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alphabetize when data changes
Thanks for the suggestion, however, I need it to include the contents of B1
with A1, because it is in B1 that I have which class # the name in A1 is in. Is there any quick way to change the code to sort A1 alphabetically include B1. Hopefully I am being clear enough. Thanks! "Simon Lloyd" wrote: Try this, it re-alphabetises from cell A1 when a new entry is added, paste this code directly on to the code sheet for sheet you want this to happen on. Regards Simon Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Column = 2 Then 'This tells the sub not to work if the column is number 2 or more Exit Sub ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if column 1 Columns("A:A").Select 'This is your column range and the A1 below is telling the sort where to start Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=499263 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alphabetize when data changes
how about changing
Columns("A:A").Select to Columns("A:B").Select -- Regards, Tom Ogilvy "Chris" wrote in message ... Thanks for the suggestion, however, I need it to include the contents of B1 with A1, because it is in B1 that I have which class # the name in A1 is in. Is there any quick way to change the code to sort A1 alphabetically include B1. Hopefully I am being clear enough. Thanks! "Simon Lloyd" wrote: Try this, it re-alphabetises from cell A1 when a new entry is added, paste this code directly on to the code sheet for sheet you want this to happen on. Regards Simon Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Column = 2 Then 'This tells the sub not to work if the column is number 2 or more Exit Sub ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if column 1 Columns("A:A").Select 'This is your column range and the A1 below is telling the sort where to start Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=499263 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alphabetize when data changes
Duh, I should have noticed that. It does work perfectly now! Thanks a lot!
"Tom Ogilvy" wrote: how about changing Columns("A:A").Select to Columns("A:B").Select -- Regards, Tom Ogilvy "Chris" wrote in message ... Thanks for the suggestion, however, I need it to include the contents of B1 with A1, because it is in B1 that I have which class # the name in A1 is in. Is there any quick way to change the code to sort A1 alphabetically include B1. Hopefully I am being clear enough. Thanks! "Simon Lloyd" wrote: Try this, it re-alphabetises from cell A1 when a new entry is added, paste this code directly on to the code sheet for sheet you want this to happen on. Regards Simon Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Column = 2 Then 'This tells the sub not to work if the column is number 2 or more Exit Sub ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if column 1 Columns("A:A").Select 'This is your column range and the A1 below is telling the sort where to start Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=499263 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Alphabetize when data changes
"Simon Lloyd" wrote: Try this, it re-alphabetises from cell A1 when a new entry is added, paste this code directly on to the code sheet for sheet you want this to happen on. Regards Simon Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Column = 2 Then 'This tells the sub not to work if the column is number 2 or more Exit Sub ElseIf ActiveCell.Column = 1 Then 'This tells the sub to only work if column 1 Columns("A:A").Select 'This is your column range and the A1 below is telling the sort where to start Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End If End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=499263 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to alphabetize data from separate cells within same row | Excel Worksheet Functions | |||
| is there a program i can use to automaticaly alphabetize data | Excel Discussion (Misc queries) | |||
| alphabetize | New Users to Excel | |||
| alphabetize | Excel Discussion (Misc queries) | |||
| How do I alphabetize data in the field list? | Excel Discussion (Misc queries) | |||
Linear Mode
